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
ActiveSheet.UsedRange.Rows.Count
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
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).row
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, _
SearchDirection:=xlPrevious).row
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."