Fill formula to last row

eolivier

New Member
Joined
Nov 25, 2015
Messages
23
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
you don't need to select the range with the formulas:
Code:
Dim lR As Long
lR = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Range("E2:G" & lR).FillDown
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,487
Assuming the values in Range("E2:G2") is text then use this script:
Code:
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

New Member
Joined
Nov 25, 2015
Messages
23
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,487
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.
 

Forum statistics

Threads
1,086,117
Messages
5,387,929
Members
402,089
Latest member
Exceliamus

Some videos you may like

This Week's Hot Topics

Top