📎 This article includes 1 downloadable practice file ↓

“Find the last row” is the first thing almost every macro needs to do: copy data below it, loop to it, or format up to it. There are several ways, and each one fails in a different situation. Here is what I use and why.

Try it live in the VBA Playground (pick “Find the last row”).

1. End(xlUp) — my default

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Starts at the very bottom of column A and jumps up to the last non-empty cell — exactly like pressing Ctrl+↑. It ignores blank cells in the middle of your data, which is why it is the safest choice.

Fails when: the column you pick is not always filled on the last row, or the sheet has a filter applied (hidden rows can be skipped). Pick a column that is always populated, such as an ID or date.

2. End(xlDown) — avoid it

lastRow = ws.Range("A1").End(xlDown).Row

Stops at the first blank cell, so one missing value in row 50 means you lose everything below it. If column A is completely empty below the header, it returns row 1,048,576. This is one of the bugs in the VBA Bug Hunt.

3. Find — best for “any column”

Dim lastCell As Range
Set lastCell = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not lastCell Is Nothing Then lastRow = lastCell.Row

Searches backwards for anything, in any column. Use it when you do not know which column is always filled. Always check for Nothing — on an empty sheet Find returns no range and your macro would crash.

4. UsedRange — quick but unreliable

lastRow = ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1

UsedRange remembers cells that were ever formatted or used, even after you clear them. After deleting data it can report rows that look empty. Fine for a rough size check, not for loops.

5. ListObject — the clean way with Tables

Dim lo As ListObject
Set lo = ws.ListObjects("SalesTable")
lastRow = lo.Range.Row + lo.Range.Rows.Count - 1
' or simply loop: For Each r In lo.ListRows

If your data is an Excel Table (Ctrl+T), you rarely need a “last row” at all. Tables grow automatically and ListRows.Add appends a new row for you.

Which should you use?

Try it yourself: step by step

1. In the Immediate window type ? Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row → 61.
2. Delete the value in A30 and try ? Worksheets("Data").Range("A1").End(xlDown).Row → 29, showing why xlDown is unreliable.
3. Try the Find method from the article on the same sheet — it still returns 61.
4. Convert the data to a table (Ctrl+T) and use ListObjects(1).ListRows.Count.
5. See it animated in the last-row playground.