excelavon

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

VBA & macros · Updated November 3, 2026

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

To find the last populated row in column A with Excel VBA, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is the worksheet you want to check. This is the simplest choice when that column reliably contains a value on every data row; use a worksheet-wide search when data may be missing from column A.

The basic End(xlUp) method

Excel worksheets have a large fixed number of rows, so the usual technique starts at the bottom of the target column and moves upward to the next non-empty cell. In VBA, End(xlUp) performs that upward navigation, much like pressing Ctrl+Up from the bottom of a column. The returned Row property is the worksheet row number, not a count of how many records exist.

For example, suppose a worksheet named Data has headers in row 1 and values in A2:A8, with A5 blank. The code below returns 8 because A8 is the last non-empty cell in column A. The internal blank does not stop the search.

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

    ' Use the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

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

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

Assigning the worksheet to ws matters. Without that reference, an unqualified Cells or Rows.Count may refer to the active sheet, which can change when a user clicks elsewhere. ThisWorkbook means the workbook containing the VBA code. If the data is in a different open workbook, qualify that workbook explicitly instead.

Choosing the right definition of “last row”

There is no single last row that is correct for every worksheet. A row can be last in one particular column, last among all cells containing content, or last inside a defined Excel table. Decide what should count before selecting a method.

  • Last row in a key column: Use End(xlUp) when a column such as an ID or date is filled for every record.
  • Last row with content anywhere on the sheet: Search the worksheet with Find so a missing value in column A does not hide data in another column.
  • Last row in a structured table: Use the table’s data range, which distinguishes table records from notes or other worksheet content.
  • Last row with a displayed value: Choose a search that evaluates cell values rather than formulas if formulas returning an empty string should be treated as blank.

These distinctions affect what a macro does next. If it appends a new record, a row number that is too small can overwrite existing information. If it formats a range, a row number that is too large can apply formatting to unrelated cells.

Handling an empty column and a header row

If the target column is completely empty, the basic End(xlUp) expression returns 1: starting at the bottom of an empty column and moving up reaches the top cell. If row 1 contains a header but there are no records, it also returns 1. That is useful when the header is the only content, but it is not the same as saying that the column contains a data record.

For a sheet where row 1 is a header, you can treat any result below row 1 as evidence of data. For a wholly empty column, check the cell at the returned row before using the result as an append position. Here is one way to return 0 when column A contains no values at all:

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

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

    ' A1 is the only possible result when the column is empty.
    If lastRow = 1 And Len(ws.Cells(1, "A").Value2) = 0 Then
        lastRow = 0
    End If

    MsgBox "Last row, or 0 if empty: " & lastRow
End Sub

This check treats a cell containing a value in A1 as content; if A1 is a header, you may instead want to report 0 data rows when the result is 1. Decide whether the header belongs to the data for your task. To find the first free row after a header, use row 2 when there are no records; otherwise use lastRow + 1.

Finding the last row with content anywhere

When records are not guaranteed to have a value in one particular column, use the worksheet’s Find method with a search ordered by rows and running in reverse. This searches across columns and returns the last cell containing a formula or constant. Checking whether the result is Nothing handles a completely empty sheet, for which there is no last content row.

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

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search all cells by row, starting from the end.
    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

    MsgBox "Last row with content: " & lastRow
End Sub

The explicit arguments are intentional. Excel remembers several Find settings from earlier searches, including searches made through the Find dialog. Supplying the search options in the macro makes its behavior more predictable instead of inheriting settings such as searching by columns or looking only in a different part of the cell. With LookIn:=xlFormulas, a formula counts as content even if its displayed result is an empty string.

This method returns the row of the last matching cell, not necessarily the row containing the last complete record. A stray note far below a data list will therefore become the result. If the task concerns records, a consistent key column or table is usually a better definition.

Multiple columns, formulas, and blank-looking cells

If a record can have a blank in column A but must contain something in columns A, C, or F, find the last row in each relevant column and take the largest row number. This measures the last row among those selected columns; it does not count cells in unrelated columns. It is a practical alternative to searching the entire worksheet when only certain fields define a record.

Formulas need special attention. A cell containing a formula such as one that returns an empty string is not truly empty: End(xlUp) generally treats it as occupied, and the worksheet-wide search using LookIn:=xlFormulas finds the formula. If your rule is based on what a person sees, search with LookIn:=xlValues instead, then check the behavior against your actual formulas and Excel version. Values that display as blank because of number formatting are another case: the underlying cell can still contain a number, so it counts as content even though it looks empty.

Cells containing errors, numbers, dates, text, or formulas are content for the usual last-row techniques. A cell that has only formatting applied is not data for the End(xlUp) approach, and it is not a match for the Find search shown above. This helps avoid confusing the last formatted row with the last row containing data.

Excel tables and UsedRange

If the records are stored in an Excel table, a table reference can be clearer than searching a worksheet column. Tables have a defined data body, so a note or a separate list elsewhere on the sheet does not affect the table’s last data row. In VBA, the table is represented by a ListObject, and its DataBodyRange contains the data rows but not the header.

Sub GetLastTableDataRow()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    Set lo = ws.ListObjects("SalesTable")

    ' An empty table has no data body.
    If lo.DataBodyRange Is Nothing Then
        lastRow = 0
    Else
        lastRow = lo.DataBodyRange.Rows(lo.DataBodyRange.Rows.Count).Row
    End If

    MsgBox "Last table data row: " & lastRow
End Sub

Replace SalesTable with the actual table name. An empty table returns 0 in this example; a non-empty table returns its final worksheet row number. A table can have a totals row, but the data body range is intended to describe table records rather than that totals row.

UsedRange is another property people often try. It describes the worksheet’s used range, which can include cells that were formatted or edited in the past, not just current data. It may be useful for tasks that intentionally concern the worksheet’s used area, but it is not a reliable substitute for defining what counts as a record. Use a table, a key column, or Find according to the data you mean to locate.

Common mistakes and how to avoid them

  • Searching the wrong sheet: Qualify cells with a worksheet variable instead of relying on whichever sheet happens to be active.
  • Assuming the key column is complete: If records can have gaps in that column, use another reliable column or search the relevant range.
  • Confusing row number with record count: If records begin on row 2 and end on row 8, the last row is 8 but there are only 7 possible record rows, and fewer if some rows are blank.
  • Counting a header as a record: Decide whether row 1 is a header and handle a header-only sheet separately.
  • Ignoring formulas that look blank: A formula returning an empty string can still be found when searching formulas. Choose formula or displayed-value logic deliberately.
  • Using the worksheet-wide result for a specific list: Notes, summaries, or other content lower on the sheet can make a whole-sheet search return a row beyond the records.

Hidden and filtered rows still contain their values. These methods do not mean “last visible row”; they find content whether or not the row is currently visible. If a macro must act only on visible filtered records, that is a separate requirement and needs visible-cell logic rather than just a last-row calculation.

Running the VBA safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the procedure there. If the Developer tab is not visible, it can be enabled in Excel Options under Customize Ribbon. On a Mac, Option+F11 commonly opens the Visual Basic Editor; depending on the keyboard, you may also need Fn. The exact key behavior can vary with Mac keyboard settings.

Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the VBA. Save a separate copy before running a macro that changes cells or workbook structure: VBA actions cannot be undone with Ctrl+Z. The examples above only display a message, but a later step that writes to the sheet could make changes that the normal Undo history cannot reverse. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros; use the desktop app for these procedures.

Frequently Asked Questions

What is the simplest VBA code to find the last used row?
For a dependable key column such as A, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. It returns the worksheet row number of the last non-empty cell in that column. If the column is empty, it returns 1, so add an empty-column check when that distinction matters.

How do I find the last row with data in any column?
Use Find on ws.Cells with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its row, because a blank worksheet has no matching cell.

Does End(xlUp) skip blank rows?
It can move past blank cells to find the last non-empty cell above them. For example, if A8 has data, A5 is blank, and A2 has data, the result is row 8. A gap does not make the method stop at the first blank encountered from the bottom.

Why does my last-row code return 1 on an empty sheet?
The common End(xlUp) expression returns the top row when there is no content in the selected column. Test the resulting cell or use a worksheet-wide Find search that returns Nothing when the sheet has no content.

Does VBA count formulas that display nothing?
Usually, yes, when you use End(xlUp) or a Find search with LookIn:=xlFormulas, because the formula itself occupies the cell. If the intended meaning of “data” is a visible result, use value-based logic and verify that it matches the workbook’s formulas.

Do filtered or hidden rows affect the last row?
Yes, their contents still count. Finding the last row is not the same as finding the last visible row. Add separate visibility checks if the macro should work only with rows currently shown after filtering.

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.