Excel VBA: Find the Last Row in a Range

VBA & macros · Updated November 19, 2026

To find the last populated row in a specified Excel VBA range, use the range’s Find method with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. For a single column, End(xlUp) is a shorter option, but it needs extra care if the range is bounded or might be empty.

What “last row in a range” means

Before choosing a method, define what counts as the last row. In a single column, it usually means the row number of the lowest cell that contains data. In a rectangular range such as B2:D100, it generally means the lowest row in which any cell in those columns contains data. Those are different questions from finding the last used row anywhere on the worksheet.

Also decide what “contains data” means in your workbook. A cell can contain a constant, a formula, a formula that displays an empty string, or just formatting. The methods below search for cell contents, not merely formatting. The examples use worksheet row numbers, so a result of 7 means worksheet row 7, not the seventh row within the selected range.

Use End(xlUp) for a single column

For a conventional list in column B, with headers in row 1 and records starting in row 2, start at the bottom of the worksheet and move upward to the next non-empty cell. In Excel for Microsoft 365 on Windows, a macro can use this pattern:

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

    ' Work on the sheet in this workbook.
    Set ws = ThisWorkbook.Worksheets("Sales")

    ' Start at the bottom of column B and move up.
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Treat the header-only or empty list as having no data rows.
    If lastRow < 2 Then lastRow = 0

    MsgBox "Last data row: " & lastRow
End Sub

If the final record is in B27, lastRow is 27. If there are no entries below the header, the upward movement reaches row 1 and the example reports 0. That convention is useful because it distinguishes “no data rows” from a real worksheet row number. Change the test against 2 if your data starts on a different row.

This is a good fit when one particular column reliably identifies a record. For example, if every sales record has an order ID in column B, column B is a sensible anchor. If column B can be blank on a valid record while column C is filled, searching only B could stop too early; use a multi-column search instead.

Search a specific range with Find

For a bounded range, such as B2:D100, Find can search all of its cells and return the last match by row. This function returns the absolute worksheet row number, or 0 if it finds no contents:

Function LastRowInRange(ByVal searchRange As Range) As Long
    Dim foundCell As Range

    ' A missing range has no matching row.
    If searchRange Is Nothing Then Exit Function

    ' Find with this pattern expects one contiguous rectangular range.
    If searchRange.Areas.Count <> 1 Then
        Err.Raise vbObjectError + 1000, "LastRowInRange", _
                  "Pass one contiguous range."
    End If

    ' Search formulas and constants, moving backward by row.
    Set foundCell = searchRange.Find(What:="*", _
        After:=searchRange.Cells(1, 1), _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False)

    ' Find returns Nothing when the range has no matching cells.
    If Not foundCell Is Nothing Then
        LastRowInRange = foundCell.Row
    End If
End Function

To call the function, place this code in a procedure in the same VBA project:

Sub ReportLastRowInSalesRange()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim resultRow As Long

    ' Set the exact area whose contents should be checked.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set dataRange = ws.Range("B2:D100")

    resultRow = LastRowInRange(dataRange)
    MsgBox "Last row in the range: " & resultRow
End Sub

If the lowest row containing a formula or value in B2:D100 is row 18, the function returns 18, even if the match is in column D. If every cell in the range is empty, it returns 0. It accepts a single contiguous rectangle; a selection made from separate, non-adjacent areas is rejected rather than searched ambiguously.

How the Find settings affect the result

The arguments in the example are deliberate. What:="*" searches for content, SearchOrder:=xlByRows evaluates row order, and SearchDirection:=xlPrevious finds the last match in that order. After sets the starting point for the search; using the range’s first cell with the backward direction lets the search wrap through the range and reach its final matching row.

LookIn:=xlFormulas includes constants and formula cells, including formulas whose displayed result is an empty string. That can be appropriate when a formula still marks a row as part of the dataset. If your definition is based on displayed values, consider LookIn:=xlValues instead, and check the behavior against formulas in your own data. A formula returning "" can be treated differently from a genuinely empty cell depending on the search setting.

Excel remembers some Find settings from earlier searches, including searches made through the Find dialog. Supplying the important arguments explicitly makes VBA less dependent on a user’s previous search. SearchFormat:=False prevents a remembered formatting criterion from restricting the match. Avoid omitting settings merely because the macro appears to work on one computer.

Choosing between End and Find

Use End(xlUp) when one column is the reliable key and you want a compact, fast way to locate its last entry. Use Find when you need to search a particular rectangle or when valid rows can have gaps in the anchor column but content in other columns. For a multi-column area, a by-row Find search answers “what is the lowest row with anything in this range?” more directly.

  • One dependable list column: use Cells(Rows.Count, column).End(xlUp).Row, qualified with the worksheet as in the example.
  • Several columns define a record: use Find on the exact rectangular range.
  • Only displayed results count: test a Find search using xlValues and formulas that return empty strings.
  • Formatting alone should not count: do not use a method that treats formatted cells as data; search for actual contents.

There is no universal “last row” independent of the data rule. If column B holds IDs, its last row may be 27, while a note in column D on row 30 makes the last content row in B:D equal to 30. Select the method that matches the operation you plan to perform, such as resizing a table, copying records, or clearing a bounded input area.

Common edge cases and mistakes

A common mistake is using Range("B2").End(xlDown) to find the bottom of a list. An empty cell within the list can interrupt that movement, and the result may not be the last occupied row. Starting at the worksheet bottom and moving up in a known column avoids that particular gap problem.

Another trap is assuming that End(xlUp) stays inside a smaller range. If you start at B100 and the cells B2:B100 are blank but B1 contains a heading, it can return row 1. Check the returned row against your data’s first row, as the column example does, or use Find on the bounded range. A search of B2:D100 cannot accidentally include a value above row 2 or below row 100.

Do not confuse the last content row with a worksheet’s used range or last formatted cell. Formatting applied far below the data can make a sheet appear much larger, and worksheet-level “last cell” approaches can reflect that history rather than the last meaningful record. When you need the last row in a particular input area, search that area explicitly.

Finally, be precise about which worksheet the code refers to. Unqualified references such as Cells or Range may point to the active sheet, which can change while a macro runs. Qualify them with a worksheet variable, such as ws.Cells and ws.Range, so the target is clear.

Running VBA safely in Excel

These examples are for desktop Excel. In Microsoft 365 for Windows, the Visual Basic Editor can be opened with Alt+F11; on Mac, the usual shortcut is Option+F11, though keyboard settings may affect function-key behavior. The Visual Basic Editor and VBA are available in desktop Excel for Mac, but Excel for the web cannot create, edit, or run VBA macros. Use desktop Excel to run this code.

Save the workbook as a macro-enabled workbook, typically .xlsm, if it needs to retain VBA. Save a copy before running a macro: macro actions cannot be undone with Ctrl+Z, even though the examples above only report a row number. Run macros only in workbooks you trust, and do not enable macros in an unfamiliar file just to inspect its contents.

Frequently Asked Questions

How do I find the last used row in column A? Use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is the worksheet you intend to search. If row 1 is a header and there are no records, add a check such as If lastRow < 2 Then lastRow = 0.

How can I find the last row with data in several columns? Apply Find to the combined rectangular range, such as A2:F500, with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. The result is the lowest row containing a match anywhere in that rectangle.

Does End(xlUp) work if there are blank rows? Yes, for a single column it moves upward from the bottom and stops at the next non-empty cell, so blank rows between records do not normally prevent it from finding the lowest entry. It cannot detect a record that is blank in the column you chose as the anchor.

Why does Find return a row for a formula that looks blank? The cell still contains a formula, even if its displayed result is an empty string. The example searches with LookIn:=xlFormulas, so formulas count. If only visible results should count, try xlValues and validate the result with your workbook’s formulas.

Can I run this macro in Excel for the web? No. Excel for the web can preserve a workbook containing VBA, but it does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the code.

Related

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.