Most efficient way of finding the last row.

[pcarmour]

Hi.
Both of these codes find the last row in column A but which is the most efficient way (computer resources use etc.)

Range("A65536").End(xlUp).Select

OR

finalrow = Cells(Rows.Count, 1).End(xlUp).Select

Thank You
Regards,
Peter

[Paul_Hossler]

1. Old versions of Excel only have 65K rows so I don't hard code a 'magic number'

2. Your examples are wrong:



finalrow = Cells(Rows.Count, 1).End(xlUp).Select



I'm guessing you meant something like




Set finallrow = Cells(Rows.Count, 1).End(xlUp).EntireRow

... or ...

finalrow = Cells(Rows.Count, 1).End(xlUp).Row


... or ...

Cells(Rows.Count, 1).End(xlUp).EntireRow.Select




depending of whether

1. You want the last row that has data in A stored in a Range variable
2. You just want the row number of the last row that has data in A
3. You want to Select the last row that had data in A

If the last used cell in A is (e.g.) A100, and the last used cell in B is B200, these will act on row 100

[mancubus]

i don't you will notice the performance differences.

all four methods below will give you the last row with data, assuming that you have a proper, well designed table (table's topleft cell is A1 and column 1 and row 1 contain no blank cells).
run it from the VBE window and make sure the Immediate Window is open.



Sub last_row()
Debug.Print Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Debug.Print Range("A1").CurrentRegion.Rows.Count
End Sub


if you applied autofilters and the last row before autofilter is filtered, the first three will give you last row after autofilter, ie last visible row with data, but the forth will give the last row's number as if no filters have been applied.

so it depends on your requirement.

[pcarmour]

Hi Paul,
Thank you very much, you have given me a good understanding of the differences and their uses.
Regards,
Peter

[pcarmour]

Great information Mancubus, thank you. I will experiment with your and Paul's suggestions.
Regards,
Peter