Another method to extract the last row containing data in a column

Question

I now use this code to find the last row of data in Sheet1's column A.

lrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

The issue is that, whenever I use this in a table that Excel has retrieved from a database, it always selects the very last row as "lrow," even if that row has no data.

When I first encountered this problem, which was a very long time ago, I learned that there was another method to design the code for finding lrow in a way that made it effective for tables that came from databases and were connected to data connections. I've been hunting for it today, but I can't seem to find it.

Answer 1

The "used range" might not be accurate. Any cell that has ever been used, that is, had data but then had the data removed, is included in the used range.

If you want to recalculate the used ranges, try running something similar to this before your function.