🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Last Row in Excel — Find the True End of Your Data (and the Trap in Each Method)

|

VBA Last Row in Excel — Find the True End of Your Data (and the Trap in Each Method)

TL;DR — Stop hardcoding Range("A1:A100"). Your data grows and shrinks, so find its edge at run time. The canonical idiom is Cells(Rows.Count, "A").End(xlUp).Row — it simulates pressing Ctrl+↑ from the very bottom of the column. Use Rows.Count, never a hardcoded 65536. There are four ways to ask "where does the data end?" and each answers a slightly different question: .End(xlUp) finds the last non-blank cell in one column, .End(xlDown) stops at the first gap, .UsedRange over-reports, and .Find is the only truly gap-proof one. Pick the wrong method and you process the wrong rows — with no error to warn you.

Finding the last row is the single most common thing a macro needs to do before it can loop, copy, sort, or clear. It is also where beginners write the most fragile line in the whole procedure: a hardcoded A2:A500. Add a row and the macro misses it; delete rows and it processes hundreds of blanks. Real VBA never assumes the size of the data — it measures it.

What you'll learn

  • The canonical Cells(Rows.Count, "A").End(xlUp).Row idiom, decoded piece by piece
  • Why 65536 is a landmine and Rows.Count is the fix
  • The failure mode hiding in each of the four methods (.End(xlUp), .End(xlDown), .UsedRange, .Find)
  • How to find the last column, not just the last row
  • When gaps in your data mean you must switch from .End to .Find

The mental model: your data has an edge, and Excel won't hand it to you

A worksheet is a million-row grid. Your data occupies some rectangle inside it, but Excel does not keep a tidy "last row" property you can just read. So you have to walk to the edge and ask where you landed. Every reliable technique is some version of "start from a place you know is past the data, then jump back to the last thing that's actually there."

The mental image for the canonical idiom is a person standing at the very bottom of a column pressing Ctrl+↑: the cursor flies up and stops on the last cell that has content. That is exactly what .End(xlUp) does in code.

Sub FindLastRow()
    Dim ws As Worksheet
    Set ws = ActiveSheet

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

    Debug.Print "Last row with data in column A: " & lastRow
End Sub

Read it inside-out: ws.Rows.Count is the number of rows on the sheet (1,048,576 on modern Excel). Cells(that, "A") is the very bottom cell of column A. .End(xlUp) jumps up to the last non-blank cell. .Row reads off its row number. That one line is the workhorse — but only if you understand the trap it carries.

The rule that saves you from the worst last-row bug: never hardcode 65536

For a decade, tutorials wrote Cells(65536, 1).End(xlUp).Row, because 65,536 was the last row of the old .xls format. Modern .xlsx sheets have 1,048,576 rows. If your data runs past row 65,536 and you start your jump-up from 65,536, you begin above part of your data and silently return a last row that's too small — cutting your loop off in the middle of the table.

' FRAGILE — starts above any data below row 65536
lastRow = Cells(65536, 1).End(xlUp).Row

' CORRECT — always starts from the true bottom of THIS sheet
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Rows.Count reads the real row count of whatever sheet the code runs on, so it is future-proof and version-proof. There is no scenario where hardcoding the number is better. Make Cells(Rows.Count, col) a reflex.

The rule that ties each method to its failure mode

Here is the part every tutorial skips: the four common methods do not return the same answer, because they answer different questions. Learn the failure mode of each and you'll always know which to reach for.

.End(xlUp) — last non-blank cell in one column. The default. Its blind spot is that it only looks at one column. If you pick a column that has a gap where the last row is empty (say row 500's name is blank but its amount isn't), you stop short. Choose a column that is guaranteed to be filled on every data row — an ID or a key — or find the last row across the whole table with .Find.

.End(xlDown) — first gap from the top. This is Ctrl+↓ from row 1. It does not find the last row; it finds the row just before the first blank cell. A single empty cell in the middle of your column truncates you to the top block. Never use .End(xlDown) to find the end of data — only to find the bottom of an uninterrupted run.

' A1:A10 has data, A6 is blank, A7:A10 has more data
Cells(1, 1).End(xlDown).Row     ' -> 5   (stopped at the gap — WRONG for "last row")
Cells(Rows.Count, 1).End(xlUp).Row  ' -> 10  (true last row — RIGHT)

.UsedRange — Excel's cached bounding box. ws.UsedRange is not your data; it's every cell Excel has ever touched, including cells that only carry formatting, and it does not shrink when you delete rows until the workbook is saved or recalculated. It over-reports, so a loop over UsedRange can grind through hundreds of phantom rows. It also does not necessarily start at row 1 (see the FAQ). Treat UsedRange as "roughly the whole sheet," never as a precise last-row number.

.Find — the only gap-proof method. Searching backwards for the last cell that contains anything, anywhere on the sheet is immune to internal blanks and to which column you pick:

Function LastDataRow(ws As Worksheet) As Long
    Dim c As Range
    Set c = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
                          LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious)
    If c Is Nothing Then
        LastDataRow = 0            ' empty sheet
    Else
        LastDataRow = c.Row
    End If
End Function

.Find is verbose and you must handle the Nothing case (an empty sheet), but it is the correct answer when your data has ragged edges or internal gaps. LookIn:=xlFormulas finds cells with a formula even if it currently evaluates to ""; xlValues ignores those — pick deliberately.

The judgment call: which method, when

  • Clean single-column list or a table with a solid key column → Cells(Rows.Count, keyCol).End(xlUp).Row. Fast, readable, correct.
  • Data with internal blank rows or a ragged right edge → .Find(What:="*", SearchDirection:=xlPrevious). Gap-proof.
  • "How big is roughly everything on this sheet, so I can clear it" → .UsedRange. Over-reporting is harmless when you're erasing.
  • Never use .End(xlDown) for the last row, and never read UsedRange.Rows.Count as if it were the last row number.

Finding the last column too

The same jump trick works sideways. Start at the far-right cell of your header row and press Ctrl+← in code:

Dim lastCol As Long
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

With both numbers you can build the exact data range without hardcoding anything:

Dim rng As Range
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

That is the whole point: once you can find the edges, you never type a fixed address again. To build a range that skips the header row, pair this with Offset and Resize; to grab the contiguous block in one call, see UsedRange vs CurrentRegion.

How ExcelMaster helps

Almost every "find the last row" question is really a step toward a bigger goal: loop this column and flag duplicates, copy new rows to another sheet, sum by group. ExcelMaster takes that goal in plain English — "for every row in the orders table, copy unfinished ones to the Backlog sheet" — and writes the range math for you, finding the edges of the data the robust way rather than assuming a fixed size.

You'll still write .End(xlUp) by hand inside a macro that runs on a schedule. But for the everyday "process whatever rows are there today" task, describing the outcome beats getting Rows.Count and gap-handling right every time.

Frequently asked questions

What is the best way to find the last row in VBA?

For a table with a reliably filled key column, use Cells(Rows.Count, keyColumn).End(xlUp).Row. It mimics Ctrl+↑ from the bottom of the sheet and is fast and readable. If your data has internal blank rows or a ragged edge, use Cells.Find(What:="*", SearchDirection:=xlPrevious).Row instead, because .End can stop at a gap.

Why should I use Rows.Count instead of 65536?

65536 was the last row of the old .xls format. Modern .xlsx sheets have 1,048,576 rows, so starting your upward jump at 65,536 begins above any data below that point and returns a last row that's too small. Rows.Count always reflects the real size of the current sheet.

Why does UsedRange give the wrong last row?

UsedRange is a cached bounding box of every cell Excel has ever touched, including formatting-only cells, and it doesn't shrink when you delete data until the file is saved. It also may not start at row 1, so UsedRange.Rows.Count is a count, not the last row number. Use .End(xlUp) or .Find for a precise last row.

How do I find the last column in VBA?

Mirror the last-row idiom sideways: Cells(1, Columns.Count).End(xlToLeft).Column. It jumps from the far-right cell of the header row back to the last column with content.

How do I find the last row when the column has blanks?

.End(xlUp) only inspects one column, so a blank in the last data row of that column makes it stop short. Either point it at a column that is always filled (an ID), or use Cells.Find(What:="*", SearchDirection:=xlPrevious).Row, which finds the last populated cell anywhere on the sheet regardless of gaps.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-07-30.

Related guides: VBA Offset · VBA UsedRange vs CurrentRegion · VBA Range · VBA For Loop