Find the Last Row of Data in a Range

January 15, 2002 - by Juan Pablo Gonzalez

This is my first tip of the day, and with it, i'll try to face one common question using VBA in Excel. That is

How can I find the last used row in a particular sheet?

There are several methods to accomplish this, some more exact than others, but they can all serve your needs.

One common method

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

Which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.

Another method to find the last used row in a particular column is:

LastRowColA = Range("A65536").End(xlUp).Row

But this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.

A couple extra methods are more reliable.

LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

LastRow = ActiveSheet.UsedRange.Rows.Count

This methods can be used on any sheet, not just the active sheet.