Excel VBA: Find the Last Used Row in Any Worksheet

To find the last row containing content anywhere on a worksheet in Excel VBA, use Range.Find with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Searching for * with LookIn:=xlFormulas also finds formulas whose displayed result is blank; check whether Find returned Nothing before reading the row number.

A reliable worksheet-wide method

This function searches all cells on the worksheet, starting at the bottom of the search order and moving backward by rows. It returns the row number of the last cell containing a constant or formula. If the worksheet has no such cells, it returns 0, which is a useful signal because row 1 can be a valid result.

Public Function LastUsedRow(ByVal ws As Worksheet) As Long
    Dim lastCell As Range

    ' Search by row, beginning at the bottom of the worksheet.
    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
        LastUsedRow = 0
    Else
        LastUsedRow = lastCell.Row
    End If
End Function

Place the function in a standard VBA module, such as one created with Insert > Module in the Visual Basic Editor. Pass the worksheet you want to inspect; the function does not assume that the active sheet is the right one. For example, this procedure calls it for a sheet named Data:

Sub ShowLastUsedRow()
    Dim ws As Worksheet
    Dim rowNumber As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    rowNumber = LastUsedRow(ws)

    If rowNumber = 0 Then
        MsgBox "No cells with content were found."
    Else
        MsgBox "Last used row: " & rowNumber
    End If
End Sub

Save a copy of the workbook before running a macro. This sample only reads the sheet, but VBA actions generally cannot be reversed with Ctrl+Z, so a backup is a sensible precaution. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code.

What the Find settings mean

Excel remembers several Find-dialog settings, including whether to search formulas or values and whether to match entire cell contents. Supplying the important arguments in VBA makes the search more predictable than relying on whatever settings a user last selected in the Find dialog. The settings in the example have specific roles:

What:="*" asks Excel to match text of any length. In a Find search, the asterisk is a wildcard, not a literal star.
LookIn:=xlFormulas searches cell contents as formulas or constants. A formula counts even when its displayed result is an empty string.
LookAt:=xlPart allows a match within the cell content rather than requiring an exact match.
SearchOrder:=xlByRows makes the search proceed through rows, which is essential when the goal is the greatest row number across the sheet.
SearchDirection:=xlPrevious searches backward from the specified starting point.
SearchFormat:=False prevents a previous format condition in Excel’s Find settings from restricting the search.

The result is a Range object, not a row number. The .Row property supplies the row number, while the Is Nothing test handles an empty worksheet safely. The search covers the worksheet’s cells, including hidden rows and columns; filtering or hiding a row does not remove its content from the search.

Choose what counts as “used”

There is no single definition of a “used row” that fits every workbook. A worksheet may contain a report table in columns A through D, notes far to the right, formulas copied down thousands of rows, or formatting applied to otherwise empty cells. Decide which of these should count before choosing a method.

The worksheet-wide function treats any constant or formula anywhere on the sheet as content. For example, if a table ends on row 40 but a note in column X appears on row 500, it returns 500. That is appropriate when you need the last content row anywhere, but it may be wrong when you mean the end of the table or the last record in a particular key column.

With LookIn:=xlFormulas, a formula such as =IF(A2="","",A2) counts even if it displays nothing. This is often desirable when the goal is to find the last row containing a formula. If instead the task is specifically to find the last nonblank displayed result, the search criteria need to reflect that definition; do not assume that “looks blank” and “contains nothing” mean the same thing in Excel.

Find the last row in one column

For a table whose records are defined by a dependable column, search only that column. For example, if every record has an ID in column A, a value far down in a notes column should not extend the record count. A common compact method uses Excel’s End behavior:

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

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

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

This starts at the bottom cell of column A and moves upward to the next occupied cell, much like pressing Ctrl+Up from the bottom of a region. It is quick and widely used, but if column A is completely empty, the expression returns 1. Therefore, it does not distinguish an empty column from a column whose only content is in row 1. If that distinction matters, test the column for content first or use a Find search limited to the column and handle a Nothing result as in the worksheet-wide function.

For a column-specific Find search, use ws.Columns("A") as the range rather than ws.Cells, while keeping SearchOrder:=xlByRows and SearchDirection:=xlPrevious. The rest of the explicit Find arguments can stay the same. This provides a clear empty-column result and still counts formulas that return an empty string when searching with xlFormulas.

How this compares with UsedRange

UsedRange describes Excel’s used range for a worksheet, but its boundary may include cells that have formatting or other remnants beyond the actual data. For instance, applying a fill color far down the sheet can make the apparent used area larger than the populated table. The range can also reflect editing history, so it is not always a dependable answer to “what is the last row with content?”

Another related option is SpecialCells(xlCellTypeLastCell), which identifies the last cell in Excel’s used-range bookkeeping. It can be useful when that boundary is what you need, but it is not a general substitute for checking content. Use Find when you need the last row containing a value or formula; use UsedRange or the last-cell special cell only when the worksheet’s used-range boundary itself is relevant.

Common problems and edge cases

Unexpectedly high row: The search may have found a note, stray value, or formula outside the main table. Search within the table’s columns or use its key column instead.
Formula cells that look empty: A formula returning "" is still a formula. With LookIn:=xlFormulas, it can be the last match. Decide whether formulas should count for your task.
Formatting mistaken for content: Formatting alone is not a value or formula for this Find search, but it can affect UsedRange and xlCellTypeLastCell.
Empty sheet or column: A worksheet-wide Find function should test for Nothing. The End(xlUp) shortcut instead reports row 1 for an entirely empty column.
Wrong worksheet: Avoid unqualified references such as Cells or ActiveSheet in reusable code. Passing a worksheet variable makes the target explicit and avoids a result changing when another sheet becomes active.
Merged cells: Merged layouts can make row-based data logic harder to interpret because the value is stored in the merged area’s upper-left cell. Prefer unmerged tabular data when a macro depends on consistent row boundaries.

If the returned row seems wrong, inspect the matching cell as well as its row number. During debugging, you can assign the Find result to a Range variable and examine its address, value, and formula in the Locals or Immediate window. That reveals whether the issue is the search method or the assumption about which cells count.

Using the result safely in a macro

A last-row result is commonly used to append data, loop over records, or define a range for another operation. Keep the zero case in mind when using the worksheet-wide function: a return value of 0 means there is no content, so it should not be used directly as a normal worksheet row. If row 1 contains headers and data begins on row 2, a result of 1 may mean the sheet has headers but no records; that is a separate business rule your macro should handle.

For example, if your code appends a record after the last populated row, calculate the result using the correct scope first. If the sheet is empty, choose deliberately whether to write in row 1 or row 2, depending on whether headers have already been created. Likewise, when looping through rows, confirm whether the first row is a header and whether blank lines inside the data are allowed. The last row tells you the boundary, not which rows are valid records.

Run VBA in desktop Excel

VBA macros run in the desktop versions of Excel, not in Excel for the web. On Windows, press Alt+F11 to open the Visual Basic Editor; on Mac, the usual shortcut is Option+F11, though some keyboards require Fn as well. You can also access the editor through the Developer tab if it is enabled. Put the function and any calling procedure in a standard module, then run the calling procedure from the editor or Excel’s macro interface.

Excel for Mac supports VBA, but keyboard mappings and security prompts can differ from Windows. The worksheet functions and object model used here are standard VBA/Excel features; the code does not depend on Windows-only APIs. If a workbook comes from an untrusted source, do not enable its macros without understanding what they do.

Frequently Asked Questions

What is the best VBA method to find the last used row?
For the last row containing content anywhere on a worksheet, use Cells.Find with SearchOrder:=xlByRows and SearchDirection:=xlPrevious, then check for Nothing. Use a column-specific range when only one column defines the records.

Does End(xlUp) find formulas that return blank?
It generally treats a cell containing a formula as occupied, even when the formula displays an empty string. That is useful when formulas count as content, but it can place the result below the last visibly populated record.

Why does my last-row code return 1 on an empty column?
Cells(Rows.Count, column).End(xlUp).Row starts at the bottom and moves upward. With no occupied cell to stop at, it reaches the top of the column. Check whether the column is empty or use Find and handle the no-match result.

Does Find count formatting-only cells?
The example searches for cell content, so formatting by itself is not a match. Formatting can influence the used-range boundary, which is one reason UsedRange may not equal the last row containing data.

Can I use this macro in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA macros. Open the workbook in desktop Excel to use the Visual Basic Editor and run the code.

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.