If you use the most common approach of
ActiveSheet.UsedRange.Rows.Countto count the last row of data in a sheet, you will get the wrong row value if there are blank rows anywhere before the first used row.
For example, put a value in a cell on row 10 of a blank worksheet and use ActiveSheet.UsedRange.Rows.Count . You will get "1" instead of "10".
Some people use
ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row -1but this will count down to any formatted cell after cells with content in them. For example, put a value in cell A10 and then change the fill color of cell A20. This method will return "20" instead of "10"
If you use
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).rowas is also commonly suggested, you will also get improper values under some circumstances. xlCellTypeLastCell seems to track NOT a cell that has a value in it, but cells that have been modified.
For example, put a value in cell A1 and run
msgbox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).rowfrom a macro. Then select any cell below that and go Edit->Clear->All and run the macro again. The last row value returned will now be whatever the cleared cell's row was.
I use a method provided here; I have not yet found a situation where it has failed to give the proper result of the last cell with content in it, regardless of formatting:
ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).rowYou can compare results yourself with this simple subroutine, ran on a new worksheet:
ActiveSheet.Cells(10, 1).Value = "test" MsgBox ActiveSheet.UsedRange.Rows.Count ActiveSheet.Cells(1, 1).Value = "Blah" ActiveSheet.Cells(2, 1).Value = "hai" ActiveSheet.Cells(10, 1).Clear MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row ActiveSheet.Cells(8, 1).Interior.Color = 0 MsgBox ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1 ActiveSheet.Cells(5, 1).Value = "Whee" MsgBox ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
I can confirm that the above is true and works with Excel 03; I am unable to test other versions.