Find last used cell in Excel with VBA

A few common requirement in VBA Excel is to find the last used row on a sheet. There are a few different ways to do this, but unfortunately only one works consistently (In Excel '03; I haven't tested in any other versions)

If you use the most common approach of
to 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 -1
but 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
as 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).row
from 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, _
You 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.

0 things about

Find last used cell in Excel with VBA

Post a Comment

Copyright 2012 Phile not Found. See About
Powered by Blogger

"Whenever you find that you are on the side of the majority, it is time to pause and reflect."