Hi everyone,
I have been using a bit of code to determine the last used row in a worksheet.
FinalRow = Cells(Rows.Count, 5).End(xlUp).Row
The '5' indicated the number of the column to look at, so Column A would be 1 etc.
This works great most of the time, but then I had a situation where, quite randomly depending on the actual data received, some columns contained blank cells. This isn't a problem most of the time, as long as the bottom cell in the column I am checking is not blank, but if that one happens to be an empty cell then the 'FinalRow' is less than the actual last used row in the sheet.
I've now found an alternative which some of you may find useful, which gives me the last active row, rather than the last active cell in a particular column:
FinalRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Hope this may be helpful 🙂
Tony
Thank you for sharing.
You will find that the used range will include all formatted cells in the returned range, even if they are blanks.
You can try that in a completely new sheet, add all borders to cell G16 for example, your code will return 16 as the last row.
The most reliable method that returns correct results, even for tables, ignoring cell formattings is the Cells.Find method:
LastRow = ActiveSheet.Cells.Find("*", ActiveSheet.Cells(1, 1), , , xlByRows, xlPrevious).Row
