O'Reilly logo
  • sophia zhou thinks this is interesting:

One better solution is to start at the bottom of the worksheet and look for the first non-blank cell with:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

That method could fail if the very last record happens to have the blank row. If the data is non-sparse enough that there will always be a diagonal path of non-blank cells to the last row, you could use:

FinalRow = Cells(1,1).CurrentRegion.Rows.Count

If you are sure that there are not any notes or stray activated  cells below the data set, you might try:

FinalRow = Cells(1, 1).SpecialCells(xlLastCell).Row


Cover of Excel® 2013 VBA and Macros


different ways to select last row(incl blank/non blank region)