Excel Marco Fill Down Dynamic Range
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 |
Excel Marco Fill Down Dynamic Range
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 |
Excel Marco Fill Down Dynamic Range
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 |
All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com