Fill formula to last row

[eolivier]

Hello,

I do not know how to write code, but only how to record macros. I have 3 adjacent cells with vlookup formulas (E2, F2, and G2), and i want the formula to fill to last row with data. Each time the macro is ran, it will start in the same spot (E2:G2), but will have a different end point depending on how many rows of data are being added.

Here is the code that was recorded:

Range("E2:G2").Select
Selection.AutoFill Destination:=Range("E2:G2519")

How can i change the destination range to go to the last row, and not a specific range?

Thanks,

Erik

 

[JoeMo]

you don't need to select the range with the formulas:

Dim lR As Long
lR = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Range("E2:G" & lR).FillDown

 

[My Aswer Is This]

Assuming the values in Range("E2:G2") is text then use this script:

Sub Fill_Down()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "G").End(xlUp).Row - 2
Range("E2:G2").Resize(Lastrow).Value = Range("E2:G2").Value
End Sub

 

[eolivier]

Thank you. After doing more research, this is the solution I came up with...

Dim lastrow As Long
lastrow = Range("D" & Rows.Count).End(xlUp).Row
...
Range("E2:G2").Select
Selection.AutoFill Destination:=Range("E2:G" & lastrow)

 

[My Aswer Is This]

Glad to see you figured that out on your own.
I was in the process of sending you an answer in case the values were formulas but a phone call interrupted me and then I saw you came up with a solution. I think using places like Mr. Excel are excellent for learning I just hope people do not just look for scripts to copy and not attempt to learn scripting. Sort of like the old adage. Teach a man to fish and I think you know the rest.