Forum Discussion
Get Macro to select Last Row entry rather than a fixed one
Hi There.
I made a macro which polishes some data I regularly import from the Web. However, its is hard-coded to select rows till 894. I wish to make it flexible, such that any entry which is the last in that data gets selected and all the data gets converted to a table. I have attached a picture of the part of Macro where 894 figure comes up. I am not able to make it flexible though.
Kindly help.
Thanks.
Try identifying the last row by adding the following code (the example uses column A for finding last row but use a column which you know will always be populated in your data):
Dim Lrow As Long
With ActiveSheet
Lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Then you can address the range dynamically using:
Range("$A$1:$G$" & Lrow)
It's hard to tell unless we see what's the layout of your data. Is there any blank row or column in between the data set?
Select the cell A1 and press Ctrl+* and if it selects all the desired data then you can use CurrentRegion Property like this instead of hard coding the range.
Range("A1").CurrentRegion
- Hello Sir. The data is in a table and no empty rows are there in the columns i am selecting. Anyways I tried the method suggested by someone else here and it worked. Thank you for the response.
Try identifying the last row by adding the following code (the example uses column A for finding last row but use a column which you know will always be populated in your data):
Dim Lrow As Long
With ActiveSheet
Lrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Then you can address the range dynamically using:
Range("$A$1:$G$" & Lrow)
