Get Macro to select Last Row entry rather than a fixed one

[Jodhvir]

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. 

[#2]

@Jodhvir 

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

[#3 · Accepted solution]

@Jodhvir 

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)

[#4]

@Charla74 

Thnku so much. This worked like a charm.

[#5]

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.