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.