Find Last Empty Row in Excel VBA: Reliable Methods

VBA & macros · Updated November 20, 2026

To find the next empty row after the data in an Excel column, use End(xlUp) from the bottom of that column, then add 1. For the last row containing anything anywhere on a worksheet, use Range.Find instead; the right method depends on what you mean by “last empty row.”

What “last empty row” usually means

An Excel worksheet contains 1,048,576 rows, so there is no single final empty row: after any data, many rows remain blank. In practice, people asking for the “last empty row” usually want the next row available for new data, or the last row that currently contains data so they can calculate what comes next. Those are related but different results.

It also matters whether you mean a blank in a particular column, a completely empty row across the whole sheet, or the first gap inside a list. A row can be empty in column A while containing a value in column B. Similarly, a cell with a formula that displays an empty string may look blank but still contain a formula. Decide what “empty” means for your task before choosing code.

  • Next row after a list in one column: Find the last populated cell in the key column and add 1.
  • Last row with content anywhere: Search the worksheet for the final cell containing a constant or formula.
  • First blank within a list: Examine the rows in the list’s expected range and locate a gap; do not simply add 1 to the last used row.

Find the next empty row in a key column

This is the common choice for a table where one column, such as an ID or name column, is reliably filled on every record. The code below treats column A as that key column. It returns row 1 if column A is entirely empty; otherwise, it reports the first row after the last nonempty cell in A.

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

    ' Change Data to the worksheet name in your workbook.
    Set ws = ThisWorkbook.Worksheets("Data")

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

    If lastRow = ws.Rows.Count Then
        MsgBox "Column A has no row available after its last entry."
    Else
        MsgBox "Next empty row in column A: " & (lastRow + 1)
    End If
End Sub

ws.Rows.Count gets the final row number for that worksheet, and End(xlUp) moves upward from the bottom of column A to its last nonempty cell. The empty-column check is important: without it, starting at the bottom and moving upward can lead to row 1 even when the column contains nothing, which makes it easy to confuse the last data row with the next available row. With a header in A1 and no records yet, this example reports row 2.

Change both references to column A if your key column is different. Use a column that is populated for every record; if some records have a blank key, this method may return a row above other data elsewhere on the sheet.

Find the last row containing content anywhere

When data may appear in several columns, search the whole worksheet. Find can locate the last cell with a constant or formula, and setting its search options explicitly makes the result less dependent on settings left over from Excel’s Find dialog.

Sub FindLastContentRowOnSheet()
    Dim ws As Worksheet
    Dim lastCell As Range

    ' Change Data to the worksheet name in your workbook.
    Set ws = ThisWorkbook.Worksheets("Data")

    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
        MsgBox "The worksheet contains no values or formulas."
    Else
        MsgBox "Last row with content: " & lastCell.Row
    End If
End Sub

Here, SearchOrder:=xlByRows makes the search identify the final row containing a match, and SearchDirection:=xlPrevious searches backward from the specified starting point. The result is the row number of the last matching cell, not the next empty row; add 1 when you specifically need a row after all content. For example, if the final content is in row 18, the last used row is 18 and the next row is 19.

This search includes formulas because LookIn:=xlFormulas searches formula contents. A formula such as one returning an empty string can therefore count as content even though the cell appears blank. If you want to ignore formulas that display nothing, the rule needs to be based on displayed values instead, and the search should be adapted to that requirement.

Choose the method that matches your layout

Use the key-column approach for a well-formed list with a dependable ID or other required field. Use the worksheet-wide search when records can have blanks in the key column, or when relevant content may extend across different columns. Neither method can infer which cells belong to your dataset: an old note far below the table is still content and may affect the result.

For a formal Excel table, another sensible approach is to add records through the table’s ListRows.Add method rather than calculating a worksheet row yourself. That lets Excel expand the table and carry its structure forward. It is especially useful when the table has filters, calculated columns, or formatting that should apply to new records. Row-finding code remains useful when the worksheet is not organized as a table or when a process specifically needs a worksheet row number.

Find a blank row inside a list

A blank in the middle is not the same as the next row after the list. Suppose records occupy rows 2 through 20, but row 8 is empty. The key-column method still finds row 21, because it looks for the last populated cell. It does not fill the gap at row 8.

If you need the first truly empty cell within a known range, loop from the first data row to the last data row and test each cell in the key column. For example, you could set a starting row of 2 and use the last data row found by the key-column method as the loop limit. A test with IsEmpty identifies cells that contain no value or formula. It does not treat a formula returning an empty string as truly empty. Choose a different test if “looks blank” is your intended definition, and decide what to do if there are no gaps: typically, use the row after the last record.

Common problems and edge cases

  • Blank rows in the middle: End(xlUp) finds the last populated cell, not the first gap. Search the list range when gaps matter.
  • Formulas that display nothing: A formula is still present. The result may count as used even when the cell looks empty.
  • Formatting below the data: Formatting alone is not the same as a value or formula. A search for * will not treat formatting by itself as matching content.
  • Content in another column: A key-column search ignores other columns. Use a worksheet-wide search if any column can determine the final row.
  • Wrong worksheet reference: ThisWorkbook refers to the workbook containing the VBA code. If the macro is stored in another workbook, make sure that is the workbook you intend to inspect.
  • Sheet-name mismatch: Replace Data with the exact worksheet tab name. A nonexistent name causes a run-time error.

There is a related misconception that Excel’s displayed “used range” always equals the last row with meaningful data. It can be affected by past editing and formatting, and it is not the same question as finding the last value in a chosen column. For dependable row logic, explicitly search the cells or column that define your data rather than assuming the worksheet’s apparent boundary is the data boundary.

Run the VBA safely in Excel

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste a procedure into the module. On a Mac, the editor is available through Tools > Macro > Visual Basic Editor; Option+F11 may open it, though some keyboards require the Fn key as well. If the Developer tab is not shown, enable it through Excel’s ribbon preferences or options.

Run a selected procedure from the editor, or use Excel’s macro interface to choose its name. Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z on Windows; on Mac, Command+Z is the usual Undo shortcut, but it does not reverse changes made by a macro either. The examples above only display a message, but a macro that writes or clears cells can make changes that require restoring a saved copy.

If you want to retain VBA, save the workbook in a macro-enabled format such as .xlsm. Only enable macros in files you trust. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros; use the desktop Excel application for these procedures.

Frequently Asked Questions

How do I get the next empty row in Excel VBA?
Find the last nonempty cell in a dependable key column with Cells(Rows.Count, column).End(xlUp).Row, then add 1. Check separately whether the column is completely empty so you can handle a new list correctly.

Does End(xlUp) find the first blank row?
No. It finds the last nonempty cell when starting from the bottom of a column. Adding 1 gives the row immediately after that cell, but it does not locate empty gaps above it.

How can I find the last used row across all columns?
Use Find with a wildcard, search by rows, and search backward. Check whether the returned range is Nothing before reading its row number, because an empty worksheet has no match.

Will formulas that return an empty string count as empty?
Not when searching with LookIn:=xlFormulas; the formula itself is content. For a key-column test, End(xlUp) also recognizes a cell containing a formula, even if its displayed result is blank.

Why does my macro return a row below the visible table?
There may be a value, formula, or note elsewhere in the searched column or worksheet. Check the exact search area and decide whether the macro should follow one key column, all worksheet content, or a defined table range.

Practical rule of thumb

For a conventional list, start with the key-column method and treat its result plus one as the next row. Switch to a worksheet-wide Find when data may extend beyond that column. If you need to reuse blank gaps, search within the list instead. Stating the range and the meaning of “empty” is what makes the result reliable—not simply using a particular VBA command.

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.