Excel VBA: Find the Last Row With Data in a Column

In Excel VBA, the usual way to find the last occupied row in one column is to start at the bottom of that worksheet column and use End(xlUp). For a more flexible search, use Range.Find; it can locate the last matching cell in a column or across the entire worksheet.

What “last row with data” means

Before choosing a method, decide what should count as data and where to look. A worksheet can have data in column A down to row 20 and a note in column H at row 100. The last row in column A is 20, while the last row containing something anywhere on the sheet is 100. Those are different questions and need different search ranges.

There is also a difference between a cell that looks empty and one that is actually empty. A cell can contain a formula that displays an empty string, a space, or a value hidden by formatting. Excel may treat that cell as occupied even when it appears blank. This matters when a macro appends records, sizes a print range, or checks whether a table has reached its final record.

For a data list with a header in row 1, a result of 1 often means the column has no records below the header. A result of 0 is useful when a search finds no occupied cells at all. Decide which convention your later code needs rather than assuming every “last row” routine returns the same answer for an empty column.

The quick method for one column: End(xlUp)

End(xlUp) behaves like selecting the bottom cell in a column and pressing Ctrl+Up: Excel moves upward to the next occupied cell. It is concise and efficient when the column is the one that reliably identifies your records. The following example checks column A on a worksheet named Data:

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

    ' Change Data if your worksheet has a different name.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Move up from the bottom of column A to its last occupied cell.
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

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

ws.Rows.Count evaluates to the worksheet’s final row number, so the code does not hard-code a row limit. Column 1 is column A. If A1 is the only occupied cell, the result is 1; if A1:A25 contain values, it is 25. If the entire column is empty, this navigation pattern returns 1, so add an empty-column check if that result would be misleading in your workflow.

To find the last record based on another key field, change the column number. For example, use 3 for column C. A reliable key column is usually preferable to scanning every column: if column A contains an ID for every record but column D is optional, the final occupied cell in D may be above the final record.

Use Find for a more explicit search

Find is useful when you want an explicit “no match” result or need to search across a broad range. It returns a Range object for the last matching cell when searching in reverse. The code below searches column A and sets lastRow to 0 if it finds nothing:

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

    ' Change Data if your worksheet has a different name.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search column A from the bottom, including formulas.
    Set hit = ws.Columns(1).Find(What:="*", _
        After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False)

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

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

The arguments are deliberately specified. Excel remembers some Find dialog settings between searches; omitting options can make a macro’s results depend on a previous manual search. SearchDirection:=xlPrevious searches backward, and SearchOrder:=xlByRows makes the search proceed by rows. LookIn:=xlFormulas means formulas are considered, including formulas whose displayed result is an empty string.

To find the last occupied row anywhere on the sheet, change the search range from ws.Columns(1) to ws.Cells, then use the matched cell’s Row property. If you also need the final occupied column, use its Column property. A sheet-wide search answers a different question from checking one key column, so select the range to match the job.

Formulas that display blanks and other edge cases

The choice of LookIn affects what Find considers a match. With xlFormulas, a cell containing =IF(B2="","",B2) still contains a formula and can be found even when it displays nothing. This is often right when the question is “which cells contain formulas or entries?” It may be wrong if the intended answer is “what is the last row with a visible, nonblank result?”

Switching to LookIn:=xlValues searches displayed values instead, but it is important to test the behavior against the specific formulas and data in your workbook. A cell containing a single space is not empty, and formatting such as a custom number format can make a value appear blank without removing it. No last-row method can infer the meaning of a cell from appearance alone.

Hidden and filtered rows are another common source of confusion. These methods search cells; they do not mean “last visible row.” A row hidden manually or by an AutoFilter can still contain data and be returned. If you need the last visible record, first define visibility as part of the requirement and use logic that checks row visibility in addition to cell contents.

Choosing between End, Find, and UsedRange

Use End(xlUp) when one known column reliably marks every record and you want a short, fast routine. Use Find when you need to detect an empty search range cleanly, search across multiple columns, or control whether formulas or values count. The most important choice is the search range: the whole worksheet can include unrelated notes, while a single column can miss records if that column has gaps.

UsedRange is not a direct substitute for either method. It describes the worksheet’s used area, which can extend beyond the meaningful data because cells were previously formatted or edited. Also, UsedRange.Rows.Count is a count of rows in that range, not necessarily the worksheet row number of its last row: if the used range starts at row 5 and contains 10 rows, the final row is 14, not 10. UsedRange can help inspect the broad area Excel considers used, but verify its starting row and whether formatting has expanded it before relying on it.

One consistent record column: use End(xlUp) and the column’s number.
Need an explicit empty result or a broad search: use Find and test whether the returned Range is Nothing.
Need the final used area for a different task: inspect UsedRange, but do not assume its row count is an absolute row number.

Common problems and how to avoid them

A frequent mistake is searching the wrong column. If an optional field has blanks near the bottom, End(xlUp) on that field reports an earlier row even though later records exist. Choose an ID, date, or other field that is filled for every record, or use a deliberate multi-column search if no single field is dependable.

Another mistake is assuming a completely blank column makes End(xlUp) return 0. It normally returns row 1 because the upward navigation ends at the top of the column. If your code needs 0 for an empty range, use Find and check hit Is Nothing, or add an appropriate emptiness check. A full-column count can be simple but may do extra work in a large workbook.

Do not confuse the last data row with the last formatted row. Borders, fills, or previously entered content can extend Excel’s used area without representing a record. Similarly, do not use Ctrl+End as proof that the last cell it selects contains data: it navigates to Excel’s remembered last-used cell, which may be affected by formatting and past edits.

Finally, qualify worksheet references. Code that uses Cells or Rows.Count without a worksheet qualifier can act on whichever sheet is active. Using ws.Cells and ws.Rows.Count makes the target explicit and reduces surprises when a user switches sheets or a macro activates another sheet.

Running the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic, choose Insert > Module, paste the code, and change the worksheet name if needed. Run it from Developer > Macros after selecting the procedure. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).

Excel for the web does not run VBA macros; open the workbook in the desktop Excel app to use this code. Excel for Mac supports VBA in the desktop app, though the editor and ribbon layout can differ. Save a copy of the workbook before running any macro: VBA actions cannot be undone with Ctrl+Z, and a larger macro may change or delete cells beyond the example’s message box.

Frequently Asked Questions

How do I find the last used row in the entire worksheet?

Use Find on ws.Cells, set SearchOrder:=xlByRows and SearchDirection:=xlPrevious, then read the matched cell’s Row. Check whether the result is Nothing before reading it. This includes entries in columns outside your main data table, so confirm that a sheet-wide search is what you want.

Does End(xlUp) count formulas that return an empty string?

Yes. A formula is still in the cell even if its displayed result is an empty string, so upward navigation can stop at that row. If you need to distinguish formulas from displayed values, choose a Find setting and test it against your workbook’s formulas.

Why does my last row result return 1 when the column is empty?

Starting at the bottom and moving up reaches the top of an entirely empty column, so the returned row is 1. Use a Find search and test for Nothing if you want an empty column to produce 0 or another explicit no-data result.

Can I find the last row in an Excel table?

Yes. If the table is a ListObject, its DataBodyRange represents its data rows, excluding the header. An empty table can have no data body, so check whether DataBodyRange is Nothing before accessing its rows. For a normal worksheet range, use End or Find as shown above.

Will the macro ignore filtered or hidden rows?

No. The examples search for occupied cells, including cells in hidden and filtered rows. If the requirement is the last visible record, add a separate visibility check; “last occupied” and “last visible” are not equivalent.

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.