Excel VBA: Find the Bottom Row of Data in a Sheet

To find the bottom row in a particular Excel column, start at the worksheet’s last row and move upward to the first non-empty cell: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. For data spread across several columns, use Find across the relevant range so the result is the last row containing something in any of those columns.

What “bottom row” means in VBA

There is no single bottom row until you decide what counts as data and where to look. A worksheet can have a customer ID in column A on row 40 and a note in column F on row 55, with column A blank below row 40. The last row in column A is 40, but the last row across columns A through F is 55. Choose the version that matches the task your macro will perform.

Also decide whether a header counts. If row 1 contains column names and there are no records below it, the last non-empty row is still row 1. A macro that needs the next record row may need to return row 2 in that situation, rather than simply using the last-row result plus one.

Use End(xlUp) for one column

Range.End(xlUp) is the standard concise method when one column reliably indicates whether a record exists. It behaves like starting at the bottom of that column and moving upward to the next non-empty cell. ws.Rows.Count means the final row available on the worksheet, so the code adapts to the worksheet’s row limit instead of hard-coding a number.

For example, with records identified by values or formulas in column A, this macro reports the last occupied row in that column:

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

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Handle the case where column A has no values or formulas.
    If Application.CountA(ws.Columns("A")) = 0 Then
        MsgBox "Column A is empty."
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    MsgBox "Last occupied row in column A: " & lastRow
End Sub

Change "Data" to the exact worksheet name and "A" to the column that defines a record. CountA prevents an entirely empty column from being mistaken for a column whose last row is 1. It counts formulas as non-empty even when a formula displays an empty string.

Find the last row across several columns

If any field in a row can contain the final record, searching only one column can miss it. The following version searches columns A through F, by rows, and takes the row number of the last match. It looks in formulas, so a formula cell is counted even if its displayed result is blank.

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

    Set ws = ThisWorkbook.Worksheets("Data")

    Set hit = ws.Range("A:F").Find( _
        What:="*", _
        After:=ws.Range("A1"), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If hit Is Nothing Then
        MsgBox "There is no data in columns A through F."
        Exit Sub
    End If

    lastRow = hit.Row
    MsgBox "Last occupied row in columns A through F: " & lastRow
End Sub

The explicit Find options matter because Excel remembers some Find settings between searches. Setting the search order, direction, and format behavior makes the macro more predictable. Limit the range to the columns that make up the dataset; searching the whole sheet can include a note, helper value, or unrelated content far below the records.

How the upward search works

In the one-column expression, ws.Cells(ws.Rows.Count, "A") refers to the last cell in column A. .End(xlUp) moves upward from that cell to the next occupied cell, and .Row returns its row number. On current Excel worksheets, the final row is 1,048,576, but using Rows.Count is clearer and avoids depending on a fixed worksheet size.

There is an important edge case: if the entire column is empty, starting at the bottom and moving up still lands on row 1. That does not mean row 1 contains data. Check for an empty column first if the distinction matters. If row 1 itself is occupied and is the only content, the result of 1 is correct.

Blank rows inside a dataset do not usually cause trouble for this method. Starting at the bottom finds the lowest occupied cell, not the first gap above the records. A blank row at the bottom, however, is skipped, so the result is the last row with content rather than the worksheet’s final formatted row.

Choose a method for the job

One dependable key column: Use End(xlUp) on that column. This is compact and easy to maintain.
Records may be incomplete in the key column: Use Find across the columns that define the dataset.
A formal Excel table: Use the table’s ListObject and its data body rather than inferring the dataset boundary from the worksheet.
A task that cares about formulas: Decide whether formulas returning an empty string should count. A formula is present even when its displayed result looks blank.

A table can be useful when the data has headers and expands as rows are added. For a table named SalesTable, ListRows.Count reports the number of data rows, excluding the header. An empty table has no data body, so code should check whether DataBodyRange is Nothing before using it. Table row counts answer a different question from worksheet row numbers: a table’s third data row may be on worksheet row 8.

Common problems and misleading results

Using the wrong column: If the chosen column has blanks in the final records, End(xlUp) reports an earlier row. Pick a required identifier column, or search all relevant fields.

Confusing formatting with data: A cell can have borders, color, or number formatting but no value or formula. The methods above look for content, not merely formatting. This is one reason UsedRange can be a poor substitute: it can extend beyond the meaningful dataset because of formatting or old edits.

Counting formulas that look blank: A cell containing a formula such as =IF(A2="","",A2) is not truly empty. CountA counts it, and a search with LookIn:=xlFormulas can find it. If your definition of data excludes formulas that currently display nothing, the test must be designed around displayed values instead of formula presence.

Unexpected content far below the table: A stray note or old value in one of the searched columns makes the returned row larger. Inspect the columns included in the search and remove unrelated content or narrow the search range. Do not assume the last used row is automatically the last business record.

Getting the next row for new data

A common follow-up is to find where a macro should write the next record. If row 1 is a header and column A contains a value for every record, use the last-row result plus one when records exist. For an empty list with only a header, start at row 2. This avoids both overwriting the header and treating an empty column’s row 1 result as a record.

For example, a macro can set its destination using this logic:

Sub FindNextRecordRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim nextRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    If Application.CountA(ws.Columns("A")) = 0 Then
        nextRow = 2
    Else
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then
            nextRow = 2
        Else
            nextRow = lastRow + 1
        End If
    End If

    MsgBox "Write the next record to row " & nextRow
End Sub

This assumes row 1 is the header and column A is filled for every record. If column A can be blank in valid records, use a multi-column search to find the dataset’s bottom row and apply the same header-aware logic. Before running any macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.

Excel version and platform notes

The examples use standard Excel VBA objects available in desktop Excel for Microsoft 365 on Windows. VBA macros do not run in Excel for the web; open the workbook in the desktop application to run them. Excel for Mac also supports VBA, though the Visual Basic Editor and some platform-specific features differ. These examples use worksheet and range operations rather than Windows-only automation, so they avoid a common source of cross-platform incompatibility.

In the Visual Basic Editor, make sure the code refers to the intended workbook and sheet. ThisWorkbook means the workbook containing the macro; it is not necessarily the workbook currently selected on screen. If code is stored in a personal macro workbook or an add-in, use an explicit workbook reference for the data file instead.

Frequently Asked Questions

What is the simplest VBA code to find the last row in column A?
Use lastRow = Cells(Rows.Count, "A").End(xlUp).Row when the active sheet is definitely the intended sheet. For more reliable code, qualify both references with a worksheet variable, as in ws.Cells(ws.Rows.Count, "A").End(xlUp).Row.

Why does End(xlUp) return 1 when the column is empty?
The upward navigation reaches the top of the worksheet when it finds no occupied cell. Check whether the column contains anything first, for example with Application.CountA, if row 1 must not be treated as data.

Does End(xlUp) count formulas that display blank?
Yes. A cell with a formula is occupied even if the formula returns an empty string. If those cells should not count as records, use a test based on the displayed results or on a field that reliably identifies a real record.

How do I find the last row across multiple columns?
Use Range.Find on the dataset’s columns with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its Row property.

Is UsedRange the best way to find the last row?
Not usually when you mean the last row containing data. UsedRange can include cells affected by past formatting or edits, so it may extend below the records. Use a defined data column, a constrained Find search, or the table’s row collection instead.

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.