VBA Find the Last Row With Data in an Excel Worksheet

To find the last row in a known column, start at the bottom of that column and use End(xlUp): ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. If the column might be empty, check it first; to find the last populated row anywhere on the worksheet, use Find instead.

Fastest method: use a known column

The standard VBA shortcut is equivalent to pressing Ctrl+Up from the bottom of a worksheet column. It moves to the next cell containing content, so blank cells between records do not stop the search. This is usually the simplest choice when every valid record has an entry in a dependable column, such as an ID or date column.

For example, with a worksheet named Data and record IDs in column A, this procedure returns the last row occupied in column A. It returns 0 if that column contains no values or formulas, making the empty-column case explicit.

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

    ' Point to the worksheet containing the records.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Use 0 to represent a column with no populated cells.
    If Application.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

ws.Rows.Count asks Excel for the number of rows available on that worksheet, rather than hard-coding a row number. In modern Excel worksheets that number is 1,048,576. The resulting lastRow is a row number, not a count of records: if the final ID is in A25, the result is 25 even if the table has a header in row 1.

The sample prints the result to the Immediate window in the VBA editor. To use it in another procedure, keep the variable and replace Debug.Print lastRow with the operation you need, such as setting a range or choosing the next row for an entry.

What “last row” means

There is no single last row until you define which cells matter. A worksheet can have a final record in row 80 of column A, a note in row 95 of column H, and formatting applied much farther down. “Last row in column A,” “last row in the data table,” and “last row with anything on the sheet” are different questions and can produce different answers.

Choose a reference column based on the structure of the data. It should be filled in for every record you consider real. If column A is an optional comment field, it is a poor anchor: a later record with a blank comment will be missed. A unique identifier, transaction date, or required name is usually a better choice. If no one column is guaranteed to be filled, search the worksheet as a whole.

Also decide whether a formula that displays an empty string counts as data. A cell containing a formula such as =IF(B2=0,"",B2) is not truly empty, even when its displayed result looks blank. The choice of search method and its settings determines whether that formula is included.

Find the last populated row across the worksheet

When records can contain gaps in different columns, use the worksheet’s Find method with a search order by rows and a direction from the end. The procedure below returns the row of the last cell it finds, or 0 if it finds no content. It explicitly sets the search options because Excel can remember some Find settings from earlier searches.

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

    ' Point to the worksheet containing the records.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search formulas and constants, starting at the end of the sheet.
    Set lastCell = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

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

    Debug.Print lastRow
End Sub

With SearchOrder:=xlByRows and SearchDirection:=xlPrevious, Excel looks for the last match when moving backward through rows. The cell it finds may be in any column; lastCell.Row gives the row number you want. LookIn:=xlFormulas treats a cell containing a formula as populated even when the formula displays an empty string. If the intended definition is based on displayed values instead, change that setting to xlValues and check the result on a representative sheet.

Choose between End, Find, and UsedRange

Each approach answers a slightly different question. Use the one that matches the workbook’s layout rather than choosing a method only because it returns a plausible number on one sample.

End(xlUp) in a known column: Fast and readable when every record has a value in that column. It ignores blank gaps but cannot detect a record that has nothing in the anchor column.
Find across the sheet: Useful when content may appear in any column. It can search formulas or displayed values, depending on LookIn, and safely requires a check for Nothing.
UsedRange: Describes Excel’s used range, which can include cells that were formatted or edited and later cleared. Its bottom row is not necessarily the last row with meaningful data, so it is not the best default for finding the last record.

UsedRange can be helpful when the goal is to inspect the area Excel considers used, for example while diagnosing a workbook with an unexpectedly large file or print area. But formatting a distant row may extend that area. Clearing cells does not always make the reported range shrink immediately, either. For a last-data-row task, Find or a reliable anchor column is generally clearer.

Adapt the code to a real workbook

Change the worksheet name and column reference to fit the file. If the macro is stored in the same workbook as the data, ThisWorkbook refers to the workbook containing the macro. ActiveWorkbook refers to the workbook currently active in Excel, which can be different. Explicitly naming the workbook avoids results changing because another file was selected.

To get the next row for a new record, first decide whether row 1 is a header and whether the sheet can be empty. If data starts in row 2 and the last occupied row is 25, the next available row is 26. If the chosen column is empty, the code returns 0, so a procedure should handle that case deliberately—for instance, it may need to place the first record in row 2 rather than row 1.

For a sheet with a header in A1 and records below it, a compact alternative is to start the upward search at the bottom of the column and then apply a minimum row check. Do not assume the result is always at least 2: if there is no entry below the header, End(xlUp) may land on row 1. Add a condition that treats this as “no records” if that is how the workbook is structured.

To install and run a macro in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure into the module, and run it from the editor or the Macros dialog. Save the workbook as a macro-enabled .xlsm file if it needs to retain VBA. On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut can depend on the keyboard’s function-key settings. Excel for the web does not run VBA macros. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.

Common problems and edge cases

A frequent mistake is applying End(xlUp) to the wrong column. If the final row has data in B but A is blank, a search in A reports an earlier row. This is not a VBA error; it is a mismatch between the chosen anchor and the records’ actual structure. Either select a column that is always populated or use the whole-sheet Find method.

An entirely empty column needs special treatment. Starting at its last cell and moving upward can land at row 1, which may look like a valid result even though no data exists. The CountA check in the first example distinguishes that case by returning 0. Keep in mind that CountA counts formulas even when they display an empty string, consistent with treating formulas as occupied cells.

Another source of confusion is mistaking the last visible row for the last row containing data. The standard methods described here can find content in rows hidden manually or hidden by a filter. They do not mean “last currently visible record.” If the task is specifically limited to visible cells, that requires a separate visibility-aware approach.

Merged cells can also make a worksheet harder to interpret: a merged range’s value is stored in its upper-left cell, not independently in every row covered by the merge. Avoid using merged layouts as the basis for record detection. Finally, use a worksheet variable rather than relying on whichever sheet happens to be active; otherwise the same macro can return a different answer after a user clicks another tab.

Tables, filters, and visible records

If the data is an Excel Table, it is often better to work with the table itself than to search the entire worksheet. A table has a defined data body and can expand as records are added. Its DataBodyRange represents the rows of data without the header; when the table has no data rows, that range can be Nothing. This makes the table a useful boundary when notes or unrelated content sit below or beside the records.

Filters hide rows from view but do not remove their contents. A normal last-row search therefore includes filtered-out records, which is usually correct when the aim is to append data or process the complete dataset. If instead a report should use only records currently visible after filtering, do not assume that the worksheet’s last row is the answer; determine visible rows separately and account for manually hidden rows as well.

When a workbook has several separate blocks of information, a whole-sheet search may return the last note or summary rather than the last row of the main dataset. In that case, constrain the search to the table or relevant columns, or use the table’s own range. “Across the whole sheet” means exactly that: any matching content can determine the result.

Frequently Asked Questions

How do I find the last row in column B?
Use ws.Cells(ws.Rows.Count, "B").End(xlUp).Row. If column B may be empty, check Application.CountA(ws.Columns("B")) first and return a chosen empty result such as 0.

Does End(xlUp) skip blank rows?
Yes. It moves upward through the column to the next occupied cell, so blank rows inside a list do not prevent it from finding a lower entry. It still depends on the selected column having an entry in the last record.

How can I find the last row with data in any column?
Use Cells.Find with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its row number.

Will formatting affect the result?
Formatting alone does not make a cell contain a value for the End(xlUp) or wildcard Find examples above. It can affect what Excel reports through UsedRange, which is one reason that property may extend beyond the actual data.

Does the search include hidden or filtered rows?
Yes. These approaches find worksheet content regardless of whether its row is currently visible. A separate method is needed when only visible records should count.

Why does my result stop before the final record?
The usual cause is that the selected anchor column is blank on that record. Choose a required field present on every record or search the relevant range across columns. Also confirm that the macro is pointing at the intended worksheet and workbook.

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.