Excel VBA: changing hard coded column to dynamic range to autofill to last row

Question

Hello from an unexperienced vba user.. I'm having trouble with the code for autofill to the last row when trying to use named ranges for the column. Everything seems to work fine when I use a hard coding for the column, in this case Column CW, and what I need is to replace this column CW with a named range so that the macro will still work when adding or deleting columns in the worksheet.

I used the following named ranges:

First_Date: This is the header cell of one of the columns (in this case AP5)
Second_Row: This is the range of columns I want to copy the formulas from (AP7:CW7)
Second_Cell: The cell where I want to start to autofill (AP7)
Last_Column: This is column CW that I want to use in the code. Autofill would be up to this column and down to the last row.

After searching in different threads, I came up with the following code that seems to work fine. How can I change column CW to a named range? Or do I need to change the code?

Dim Lr As Integer
Lr = Range("First_Date").End(xlDown).Row 'Searching last row 
Rows(Lr).Insert Shift:=xlDown 'Inserting new row
Range("Second_Row").AutoFill Destination:=Range(Range("Second_Cell"), Range("CW" & Lr))

Can anyone assist me here please?

Tuckshop86 Tuckshop86 · Accepted Answer · 2014-02-25T00:11:52

This will get the job done :-)

Sub RangerFiller()

'The Cell that holds the formula B1

OriginalFormula = Cells(1, 2).Formula

'copies formula down to the last column next to B but use can use another column as 'a counting column....the column that hold the last value

Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = OriginalFormula

End Sub

Excel VBA: changing hard coded column to dynamic range to autofill to last row

3 Answers