Detecting last data row in Excel.Workbook Object

[anhn]

1. As mentioned, ActiveSheet.UsedRange.Rows.Count returns number of rows in UsedRange,
it may not indicate the last used row on the worksheet if the first row of the UsedRange is not row 1.

The correct way should be like this:

LastUsedRow = ActiveSheet.UsedRange.Rows.Count - ActiveSheet.UsedRange.Row + 1

ActiveSheet.UsedRange.Row is the first row of the UsedRange, in case if that is 1 then
LastUsedRow = ActiveSheet.UsedRange.Rows.Count - 1 + 1

However, what does "UsedRange" mean?
Yes, it means the used range on a worksheet!!!
The last row and the last column of UsedRange may NOT contain any data if they was used before with data entered then cleared or cell formated.
So, be careful when using UsedRange to find last row or last column.
This may also happen the same way when using .SpecialCells(xlCellTypeLastCell)


2. The .End(xlUp) method can give only the last row that contains data on a specified column but more reliable.
If you want to use it without worry about Excel version (2003 or 2007) then do not hard code 65536 there. Use this:

With Activesheet '-- or any sheet
    LastRowOnColumn2 = .Cells(.Rows.Count, 2).End(xlUp).Row
End With

or without using [With] block:

LastRowOnColumn2 = Activesheet.Cells(Activesheet.Rows.Count, 2).End(xlUp).Row

.Rows.Count will return 65536 on Excel 2003 or 1048576 on Excel 2007.
Similarly, .Columns.Count will return 256 on Excel 2003 or 16384 on Excel 2007.