Excel VBA Find Last Row: Reliable Methods and Examples

In Excel VBA, find the last row in one column with Cells(Rows.Count, "A").End(xlUp).Row, replacing A with the column you want to check. To find the last row containing anything anywhere on a worksheet, use Cells.Find("*") with a reverse, row-based search; the right choice depends on what “last row” means for your data.

What “last row” means in VBA

A worksheet has a fixed maximum number of rows, so Rows.Count returns the sheet’s maximum row number, not the last row of your records. On a modern Excel worksheet that number is 1,048,576. VBA code usually starts there and works upward, or searches the worksheet in reverse, to locate the last relevant cell.

There is no single last row that fits every workbook. A data list might have records in columns A through F, while notes or formulas elsewhere extend farther down. You need to decide whether to find the final nonempty cell in a particular column, the final occupied row anywhere on the sheet, or the last row in a structured table. State that rule explicitly in your code; otherwise, a technically correct result can still point to the wrong record.

Fastest method for a known data column

Use End(xlUp) when one column reliably identifies records, such as an ID column that is filled on every data row. It behaves like starting at the bottom of the column and pressing Ctrl+Up: Excel moves to the next nonempty cell. This is usually the simplest and fastest pattern for ordinary lists.

For example, with worksheet Data and record IDs in column A, the following macro returns the last nonempty row in that column. It returns 0 if the entire column is empty, which makes the empty-sheet case explicit rather than incorrectly treating row 1 as a record.

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

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Treat an entirely empty column as having no data rows.
    If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
        lastRow = 0
    Else
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    End If

    MsgBox "Last nonempty row in column A: " & lastRow
End Sub

If A1 contains a header and the only record is in A2, the result is 2. If the column has a header but no records, the result is 1; the code reports the last nonempty cell, not a count of data rows excluding headers. If you want a record count, subtract the header row only after confirming that the column contains one.

Find the last occupied row anywhere on a sheet

If a record can have a blank in the column you would otherwise check, searching the whole worksheet is safer. The Find method searches cells containing constants or formulas and can return the last matching cell by row. Set its search options explicitly: Excel remembers some Find settings from earlier searches in the user interface or VBA, so leaving arguments out can produce surprising results.

This macro looks across the Data worksheet, searches formulas as well as constant values, and reports 0 when no matching cell exists. Since the search order is by rows and the direction is previous, the returned cell is in the last occupied row on that sheet; it is not necessarily the rightmost cell in that row.

Sub ReportLastUsedRowOnSheet()
    Dim ws As Worksheet
    Dim foundCell As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search all cells, including cells containing formulas.
    Set foundCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        lastRow = 0
    Else
        lastRow = foundCell.Row
    End If

    MsgBox "Last occupied row on the sheet: " & lastRow
End Sub

With a value in B4 and a formula in G20, this search returns 20, even if the formula displays an empty string. That is often desirable when formulas count as part of the sheet’s content. If you want to ignore formulas and search only displayed values, change LookIn:=xlFormulas to LookIn:=xlValues. Be aware that what counts as a match can depend on the cell’s displayed result and Excel’s Find behavior.

How to choose between End and Find

Pick the method based on the shape and meaning of the data, rather than on which line of VBA looks shorter:

Use End(xlUp) when a designated column is consistently populated for every record. It is concise and efficient, but can stop early if that column has gaps below the last filled cell.
Use Find when records have gaps in key columns or any column can contain the final content. It scans the sheet’s cells and gives you a worksheet-wide answer.
Use a table’s data range when the records are in an Excel Table and you mean the last row of that table, not other content elsewhere on the sheet.
Avoid relying on UsedRange for a precise data boundary unless formatting and previously edited cells are intentionally part of your definition.

For a multi-column list, choosing the column with a guaranteed value in every record is usually better than searching all columns. If no such column exists, Find is more dependable. A worksheet-wide search may include a note, a stray formula, or an unrelated cell below the list, so it is not automatically the best choice just because it searches more cells.

Use a reusable last-row function

If several procedures need the worksheet-wide result, put the search in a function in a standard VBA module. This version uses the same explicit Find settings and returns 0 if the sheet has no matching cells. A calling procedure can assign the result to a Long variable and decide what to do when it is zero.

Function LastUsedRow(ByVal ws As Worksheet) As Long
    Dim foundCell As Range

    ' Search by row so the result is in the final occupied row.
    Set foundCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        LastUsedRow = 0
    Else
        LastUsedRow = foundCell.Row
    End If
End Function

For example, calling LastUsedRow(ThisWorkbook.Worksheets("Data")) returns the row number, not a Range object. Keep the worksheet reference qualified. Using ActiveSheet can make the procedure act on whichever sheet happens to be selected, which may change when a workbook has multiple tabs or code activates another sheet.

Tables, headers, and empty data

Excel Tables, also called ListObjects in VBA, offer a more specific boundary when a worksheet contains multiple lists or footnotes. A table’s data body is separate from its header and total row. If a table has no data rows, its DataBodyRange is Nothing, so code should check for that before asking for a row number.

For a table named SalesTable, the last worksheet row of its data body can be computed from the range’s starting row and row count: SalesTable.DataBodyRange.Row + SalesTable.DataBodyRange.Rows.Count - 1. The reference must be to the table object in VBA, and the expression should only be evaluated after confirming that DataBodyRange is not Nothing. This approach excludes unrelated cells elsewhere on the worksheet and automatically follows the table as it grows or shrinks.

Headers need deliberate handling. If a column has a header in row 1 but no records, End(xlUp) returns 1. If there is no header and the whole column is blank, End(xlUp) can also return 1 because the search lands at the top of the column. Use a CountA check or another explicit empty-data test when the distinction matters. For record counts below a single header, subtract one from the final row only after verifying the layout and that data begins in row 2.

Common mistakes and edge cases

Checking the wrong column: A blank in the chosen key column can make End(xlUp) return an earlier row even when other columns contain later data. Choose a reliably filled column or use Find.
Using unqualified references: Cells, Rows.Count, and Range without a worksheet qualifier can refer to the active sheet. Use ws.Cells and ws.Rows.Count to make the target clear.
Confusing content with formatting: A cell with only a fill color or border is not a value or formula found by the wildcard search. UsedRange, however, can be expanded by formatting and old edits, so it may report a much lower boundary than the actual records.
Forgetting formulas that look blank: A formula such as one returning an empty string is still a formula. With LookIn:=xlFormulas, it can be included in the search even though the cell appears blank on screen.
Using Integer for row numbers: VBA Integer cannot hold the maximum row number of a modern worksheet. Use Long for row values.
Assuming Find settings are fixed: Search options can persist between calls. Specifying LookIn, LookAt, SearchOrder, SearchDirection, and SearchFormat makes a macro more predictable.

Running and adapting the VBA code

On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Run a selected procedure with F5 from the editor, or use Developer > Macros in Excel. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Save a copy of the workbook before running a macro: macros cannot be undone with Ctrl+Z, even if the macro changes cells or other workbook content.

Save a workbook containing VBA in a macro-enabled format such as .xlsm if you need to keep its code. Excel for Mac also supports VBA, although keyboard combinations and editor access can depend on the keyboard and Excel release; use the Developer tab’s Visual Basic command if the Windows shortcut does not apply. Excel for the web can open workbooks containing macros, but it cannot create, run, or edit VBA macros. For a workbook shared with web users, do not assume the macro will execute there.

Frequently Asked Questions

What is the simplest VBA code to find the last row in column A?
Use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is a worksheet variable set to the sheet you intend to check. It finds the last nonempty cell in column A; add an empty-column check if a result of 1 would be ambiguous.

How do I find the last row with data in any column?
Use ws.Cells.Find with What set to a wildcard, SearchOrder set to xlByRows, and SearchDirection set to xlPrevious. Check whether Find returned Nothing before reading the result’s Row property.

Does End(xlUp) count formulas that return blank?
End(xlUp) navigates based on the cells in the column, and a cell containing a formula is not truly empty even if its displayed result is blank. If the distinction between formula cells and visible values matters, select a method and test it against the workbook’s formula layout.

Why does VBA return row 1 when the column is empty?
Starting at the bottom and moving upward in a completely empty column reaches the top cell, so the returned row is 1. Test whether the column contains anything first if you want an empty column to return 0.

Should I use UsedRange to find the final data row?
Usually not when you need an exact data boundary. UsedRange can include cells affected by formatting or past edits, even when they do not contain current records. Use a known key column, Find, or a table’s DataBodyRange according to what should count as 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.