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

[Yucca]

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?

 

[AlphaFrog]

Change this...
Range("CW" & Lr)

To This...
Cells(Lr, Range("Last_Column").Column)

Edit:
Also, you could find the last column just as you found the last row.
Lc = Cells(7, Columns.Count).End(xlToLeft).Column

 

[Yucca]

Thanks a million! I used the first option and it did the job, since I don't know how I would have to use Lc in this code.. What does the 7 stand for and how would I use it in the autofill code?

 

[AlphaFrog]

You're welcome.

When your in the VBA editor, you can highlight a command e.g. Cells, press F1 and you will get its' help; description, syntax and an example code.