Hello,

I am trying to run a vlookup through a macro but need the range to vary
depending on the numbers of rows of data (which will be different every
time).
Currently I have just used a fixed range in the code

Sub fillDownLookup()
Range("ah2").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-13],Lookup!R2C6:R328C7,2,FALSE)"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("AH2:AH4000").Select
Selection.FillDown
End Sub

I would appreciate any advice of sugguestions that would replace the
fixed "Range("AH2:AH4000")" with code for a dynamic range

cheers
R

Range("AH2:AH" & Range("AG" & Rows.Count).End(xlUp).Row).Select
Of course this assumes that you have data in AG4000 (or whatever the
last row happens to be in.) If not all columns contain data in every
row, then use this function to return the last used row:
Function LastRow() As Long
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function
Then use:
Range("AH2:AH" & LastRow).Select

HTH

Charles Chickering

wrote:
Hello,

I am trying to run a vlookup through a macro but need the range to vary
depending on the numbers of rows of data (which will be different every
time).
Currently I have just used a fixed range in the code

Sub fillDownLookup()
Range("ah2").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-13],Lookup!R2C6:R328C7,2,FALSE)"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("AH2:AH4000").Select
Selection.FillDown
End Sub

I would appreciate any advice of sugguestions that would replace the
fixed "Range("AH2:AH4000")" with code for a dynamic range

cheers
R

Absolutely perfect. thanks!

Die_Another_Day wrote:
Range("AH2:AH" & Range("AG" & Rows.Count).End(xlUp).Row).Select
Of course this assumes that you have data in AG4000 (or whatever the
last row happens to be in.) If not all columns contain data in every
row, then use this function to return the last used row:
Function LastRow() As Long
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function
Then use:
Range("AH2:AH" & LastRow).Select

HTH

Charles Chickering

wrote:
Hello,

I am trying to run a vlookup through a macro but need the range to vary
depending on the numbers of rows of data (which will be different every
time).
Currently I have just used a fixed range in the code

Sub fillDownLookup()
Range("ah2").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-13],Lookup!R2C6:R328C7,2,FALSE)"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("AH2:AH4000").Select
Selection.FillDown
End Sub

I would appreciate any advice of sugguestions that would replace the
fixed "Range("AH2:AH4000")" with code for a dynamic range

cheers
R