Using Named Range and Variable to Define a Range

Question

I have been trying to use a named range and variable to define a range that I want to autofill and I can only get one of the named ranges to work and not the variable in my code. The ColLetter variable is what is giving me trouble. If the I omit that variable and just put "AR" in the range the code works it is just not dynamic.

Sub test()

    Dim lastRow As Long
    Dim ColLetter As Range
         lastRow = Range("D" & Rows.Count).End(xlUp).row 'Counts rows in specific column which controls how far to autofill.
         ColLetter = Mid(ActiveCell.Address, 2, 2)
         'Range("Notes_Start").AutoFill Destination:=Range("Notes_Start:AR" & lastRow) 'This works just is not dynamic.  The AR for column is static.
         Range("Notes_Start").AutoFill Destination:=Range("Notes_Start:ColLetter" & lastRow)
End Sub

Jeanno · Accepted Answer · 2015-03-30 15:57:04Z

1

A couple of problems, Dim ColLetter As Range should really be Dim ColLetter As String, your string concatenation is wrong, change Range("Notes_Start:ColLetter" & lastRow) to Range("Notes_Start:" & ColLetter & lastRow)

answered Mar 30, 2015 at 15:57

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Johnsen Over a year ago

Thanks this fixed my code Jeanno. Syntax got me again. @FreeMan is right I should probably stop using letters for column references.

FreeMan · Accepted Answer · 2015-03-30 17:40:22Z

0

Be very careful using letters to define columns in your code.

If your active cell is A1, Mid(ActiveCell.Address, 2, 2)
- yields A$, which is what you want.
If your active cell is AA1, Mid(ActiveCell.Address, 2, 2)
- yields AA. You lose the $ and your reference is broken.

You would probably be better off using numerical references to columns:

Dim Col as integer
Col = ActiveCell.column

I'm not familiar with the Range("Notes_Start:..." syntax, but I believe that you could use Range("Notes_Start").Offset(lastrow,Col) to get what you're after. The Offset() may need a tweak of +1 or -1 to adjust to exactly where you need to be. Some quick trial & error will show exactly what you need to do.

You could use:

Dim adr() as string
Dim ColLetter as string
adr = split(ActiveCell.Address, "$")
ColLetter = adr(0)

But I'd still recommend using column numbers...

edited Mar 30, 2015 at 17:40

answered Mar 30, 2015 at 16:45

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

4 Comments

Sean Johnsen Over a year ago

Thanks for the quick reply. The "Notes_Start" is my named range which is a single cell. I am still getting a runtime error '1004': AutoFill method of Range class failed. Is this the way you intended the code below? Sub test1() Dim lastRow As Long Dim Col As Integer Col = ActiveCell.Column lastRow = Range("D" & Rows.Count).End(xlUp).row 'Counts rows in specific column which controls how far to autofill. Range("Notes_Start").AutoFill Destination:=Range("Notes_Start").Offset(lastRow, Col) 'This works just is not dynamic. The AR for column is static. End Sub

FreeMan Over a year ago

If Notes_Start is a named range, you would refer to it as Range("Notes_Start"). I though that maybe it was a large range of cells and that the Notes_Start:<row>, <col> was a syntax I was unfamiliar with for referring to a cell within that named range.

FreeMan Over a year ago

What range are you trying to refer to with Destination:=Range("Notes_Start:ColLetter" & lastRow)

Sean Johnsen Over a year ago

In this instance "Notes_Start" = $AR$8

Collectives™ on Stack Overflow

Using Named Range and Variable to Define a Range

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related