VBA Find Last Row: Reliable Methods and Examples

In VBA, find the last row in a particular column with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. To find the last row containing content anywhere on a worksheet, use Find with a search ordered by rows; the right method depends on what “last row” means in your data.

What “last row” means in VBA

A worksheet has a last possible row, but most macros need the last row that matters: the final row with a value, formula, or record in a data range. Those meanings are not interchangeable. A report may have data through row 250 in column A, while a note in column H extends the worksheet’s content to row 900. A formula that displays a blank string may also count as occupied even though the cell looks empty.

Before choosing code, decide whether the macro should inspect one reliable data column, a defined block of columns, or the entire worksheet. A column-based search is usually the simplest and quickest when every record has a value in that column. A whole-sheet search is more suitable when the relevant content might appear in different columns.

Find the last row in one column with End(xlUp)

The common VBA pattern starts at the bottom of a column and moves upward to the first nonempty cell it encounters. ws.Rows.Count supplies the final row number for that worksheet, so the code also works if a worksheet’s row limit differs from a hard-coded number.

Sub FindLastRowInColumn()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Use the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Return 0 if column A has no values or formulas.
    If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
        lastRow = 0
    Else
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    End If

    Debug.Print lastRow
End Sub

For example, if column A contains a heading in row 1 and records through row 37, the result is 37. Blank cells between records do not prevent the search from finding the last occupied cell. This makes the method more dependable than counting rows or assuming the data is continuous.

The empty-column check is important. If column A is completely blank, End(xlUp) from the bottom returns row 1, which may look like a real result. Returning 0 is a useful convention for “no occupied cells,” but your macro could instead stop with a message or handle the empty case another way.

Choose a column that represents a record

The result is only as useful as the column you search. Choose a field that is populated for every valid record, such as an ID or date, rather than an optional comment or status field. If a record’s ID is in column A, for instance, the last nonempty row in A can define the end of the list even if later columns contain occasional blanks.

Here is the shorter form when the selected column is guaranteed to contain at least one value:

Sub GetLastRowQuickly()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Debug.Print lastRow
End Sub

Use Long for row numbers. Excel worksheets can contain far more rows than fit in an older 16-bit integer, and VBA’s Long type safely holds Excel row numbers. Also qualify the range with a worksheet, as shown. An unqualified Cells or Rows reference uses the active sheet, which can produce a wrong answer if the user switches worksheets.

Find the last row with content anywhere on a sheet

When data may be present in several columns and no single column is complete, use the worksheet’s Find method. Searching backward by rows returns the row of the last matching cell in the searched range. The following example searches the entire worksheet for a value or formula.

Sub FindLastUsedRowOnSheet()
    Dim ws As Worksheet
    Dim lastCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search for content, not formatting alone.
    Set lastCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If lastCell Is Nothing Then
        Debug.Print 0
    Else
        Debug.Print lastCell.Row
    End If
End Sub

Find returns a Range, not a row number, so use lastCell.Row to get the row. If there is no match, it returns Nothing; check for that before using the result. The specified search arguments matter because Excel remembers some Find settings from earlier searches or dialog-box use. Setting the options in code makes the search more predictable.

With LookIn:=xlFormulas, formulas count even when their displayed result is an empty string, as do constants such as text and numbers. That is often appropriate when “used” means a cell contains an entry or formula. If you specifically want to search displayed values instead, use LookIn:=xlValues and consider how formulas that display blank should be treated in your workbook.

Search only the data columns when appropriate

A whole-sheet search can include titles, notes, or other content outside the table. If your records are limited to columns A through D, search that range instead. The last matching row will then be based only on those columns, so a note in column H will not extend the result.

To adapt the earlier example, replace ws.Cells.Find with ws.Range("A:D").Find and keep the explicit search settings. The returned cell still provides the row through its Row property. The range can be narrowed further, such as A:D from the heading row downward, if the worksheet contains unrelated content above the table.

Another option is to find a last row separately in each relevant column and keep the greatest row number. That can be useful when each column has a distinct purpose, but it needs extra handling for empty columns. For a straightforward “last content anywhere in these columns” question, a single Find over the defined range is usually easier to maintain.

Why UsedRange and Ctrl+End can give a different answer

UsedRange describes Excel’s recorded used area, which can be influenced by formatting as well as cell contents. A cell far below the records may have had a border, fill, or other formatting applied and later cleared. In that situation, UsedRange can extend beyond the actual data. Pressing Ctrl+End moves to Excel’s last recorded used cell, so it is not always a reliable way to identify the final row containing a value.

If you do use UsedRange, its row count is not necessarily the absolute worksheet row number. The bottom row of that range is calculated as ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1. Even that calculation describes the recorded used area, not necessarily the last row with meaningful records. For content-based searches, End(xlUp) or Find is generally clearer.

Common problems and how to avoid them

The result is 1 on a blank column: That is the normal result of moving up from the bottom of a completely empty column. Check whether the column has content first if you need 0 or another empty-data response.
The wrong sheet is searched: Set a worksheet variable explicitly and qualify every range with it. Avoid relying on whichever sheet happens to be active.
A formula that looks blank extends the result: The cell still contains a formula. Decide whether your definition of “last row” includes formulas, including formulas returning an empty string.
Formatting makes the last row seem too large: Formatting alone does not make a cell match the Find search for * with LookIn:=xlFormulas, but it can affect UsedRange and Ctrl+End.
Find behaves differently on another run: Specify the search arguments instead of relying on saved Find dialog settings. Also check that the searched range is the intended worksheet or columns.
A fixed row number stops working: Avoid hard-coding Excel’s bottom row. Use ws.Rows.Count so the starting point follows the worksheet’s row limit.

Use the result to append a new record

Once the final occupied row is known, a macro can place a new record on the following row. If row 1 contains headings and column A is the required ID column, the next row is the last row plus one. An empty-data result needs a separate rule: if the heading is already in row 1, the first record belongs in row 2, not row 1.

For example, keep the empty-column check from the first procedure, then set the destination row to 2 when lastRow is 0; otherwise, use lastRow + 1. If the sheet might already contain a heading but no records, searching the ID column returns row 1 when that heading is in the same column. In that case, adding one gives the correct first record row. Match the logic to how your sheet is structured rather than assuming every blank result means the sheet has no heading.

Before running a macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even when a macro makes an unintended edit. If the code needs to be saved in the workbook, use a macro-enabled Excel file (.xlsm) and follow your organization’s macro security rules.

Frequently Asked Questions

How do I find the last row in column A in VBA?
Use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, with ws set to the worksheet you want. Add an empty-column check if row 1 should not be returned for a blank column.

How do I find the last used row across all columns?
Use ws.Cells.Find with What:="*", SearchOrder:=xlByRows, and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its row.

Does End(xlUp) skip blank rows?
It can move past gaps and return the last nonempty cell above them. For example, if A10 is filled, A11:A20 are blank, and A21 is the starting cell, the search finds A10.

Why does my macro return row 1 when the column is empty?
Starting at the bottom of an empty column and moving upward reaches the top cell. Test the column for content first if you want an empty result represented by 0 or handled separately.

Should I use UsedRange or Find?
Use Find when the question is about the last cell containing a value or formula. Use UsedRange when you need Excel’s recorded used area, understanding that formatting can make that area larger than the actual data.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.