Excel VBA: How to Find the Next Empty Cell in a Column

To find the next empty cell below the last entry in an Excel column, use VBA’s End(xlUp) navigation and select the row after the last occupied cell. For column A with a header in row 1, the example below handles both an empty column and a column that already contains data.

Quick method: go below the last entry

This macro treats row 1 as a header and finds the next available cell after the last non-empty cell in column A. Change the worksheet name, column letter, or starting row to fit your workbook. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Sub SelectNextEmptyInColumn()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim target As Range
    Dim firstDataRow As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    firstDataRow = 2
    lastRow = ws.Rows.Count

    ' Start at the first data row if the column has no entries.
    If Application.CountA(ws.Columns("A")) = 0 Then
        Set target = ws.Cells(firstDataRow, "A")
    Else
        ' Find the last cell with a value or formula in column A.
        Set lastCell = ws.Cells(lastRow, "A").End(xlUp)

        If lastCell.Row = lastRow Then
            MsgBox "There is no row below the last entry."
            Exit Sub
        End If

        If lastCell.Row < firstDataRow Then
            Set target = ws.Cells(firstDataRow, "A")
        Else
            Set target = ws.Cells(lastCell.Row + 1, "A")
        End If
    End If

    ' Activate the sheet so the target can be selected.
    ws.Activate
    target.Select
End Sub

If A1 is the header and the last entry is in A18, the macro selects A19. If there is nothing anywhere in column A, it selects A2. If you have no header, set firstDataRow = 1. The full-column CountA check matters because using End(xlUp) on a completely blank column can lead to treating its top cell as the last cell.

What “next empty cell” can mean

The phrase describes two different tasks, and choosing the right one prevents gaps or existing records from being overlooked:

Append after the last entry: Find the last occupied cell, then use the cell below it. This is usually right for adding a new record to a continuous list.
Find the first blank gap: Start at the top of the data and stop at the first genuinely empty cell, even if there are more entries farther down.
Find a cell that looks blank: A formula such as one returning an empty string may display nothing but still occupy the cell. Decide whether formulas that display blank should count as occupied.

For example, suppose A2, A3, and A5 contain names, while A4 is blank. Appending after the last entry returns A6. Finding the first gap returns A4. Neither result is universally correct; it depends on whether the macro is adding to the end or filling holes in the list.

How End(xlUp) works

ws.Cells(ws.Rows.Count, "A") refers to the bottom cell in column A. The End(xlUp) method moves upward from there to the next cell Excel considers occupied, much like navigating upward through a worksheet region. Reading that cell’s Row gives the last occupied row number. Adding one identifies the row immediately below it.

This method is concise and fast for ordinary lists. It looks at cell contents, including formulas, not just visible text. So a formula in A20 that displays an empty string still makes A20 part of the column’s occupied content. Formatting alone, such as a fill color in an otherwise empty cell, does not make it a data entry for this navigation.

The example also checks whether the last used row is the worksheet’s final row. Excel worksheets have a fixed row limit, so there is no valid cell beneath the bottom row. The check avoids trying to select a row outside the sheet.

Find the first blank gap instead

When a blank in the middle should be filled before appending, check the data rows one at a time. This version starts at row 2, stops at the first cell for which IsEmpty is true, and selects it. If there is no gap before the last occupied row, it selects the row after that entry.

Save a copy of the workbook before running this macro as well; macro actions cannot be reversed with Ctrl+Z.

Sub SelectFirstEmptyGapInColumn()
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim lastRow As Long
    Dim firstDataRow As Long

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

    ' Stop at the first truly empty cell in the data area.
    For rowNum = firstDataRow To lastRow
        If IsEmpty(ws.Cells(rowNum, "A").Value2) Then
            ws.Activate
            ws.Cells(rowNum, "A").Select
            Exit Sub
        End If
    Next rowNum

    ' No gap was found; choose the row after the last entry.
    If lastRow < ws.Rows.Count Then
        ws.Activate
        ws.Cells(Application.Max(firstDataRow, lastRow + 1), "A").Select
    Else
        MsgBox "There is no empty row available below the data."
    End If
End Sub

This loop assumes the data starts on row 2 and the relevant list is in column A. If your header is elsewhere, adjust firstDataRow. The macro checks only through the last occupied row; it does not search every remaining row in the worksheet, because the next row after the end is already the natural append position.

Blank cells, formulas, and displayed blanks

IsEmpty tests whether a cell has no value or formula. A formula returning "" is not empty in this sense, even though the cell may look blank. That distinction matters in imported reports and worksheets that use formulas to hide output until a condition is met.

If your definition of a gap is “nothing displayed,” you can instead test a formula cell’s result, but first decide how to treat errors and spaces. A cell containing a space is not empty, and a cell displaying an error is not blank. For a basic list without error values, a test such as Len(ws.Cells(rowNum, "A").Value2) = 0 treats a formula returning an empty string as visually blank. Use that in place of the IsEmpty condition only when that is the intended rule.

Also consider whether every column defines a complete record. If column A is sometimes blank while other fields in the same row are filled, column A alone may not be a reliable indicator of the last record. In that case, select a required ID column or inspect the relevant range of columns before deciding that a row is available.

Choosing between End and a search

For a simple, continuous list, End(xlUp) is generally the clearest choice. It is easy to understand, and it avoids checking each row individually. A loop is more suitable when the first blank in the middle is the target, because the last-entry method deliberately skips gaps.

For less predictable worksheets, Excel’s Find method can locate the last cell containing a value or formula. It is useful when you want to search a specific range or make the search criteria explicit. A common caution with Find is that some search options can carry over from earlier Find operations, so reliable VBA should specify the relevant options rather than depending on the dialog’s previous settings. For a straightforward single-column list, that extra machinery is often unnecessary.

Whichever approach you use, keep the search range limited to the relevant column and worksheet. Avoid relying on the active sheet or selection to imply where the macro should work: explicitly setting a worksheet, as the examples do, makes the destination more predictable.

Adjusting the code for your workbook

Use a different column: Replace each occurrence of "A" with the required column letter, such as "D".
Use a different sheet: Replace "Sheet1" with the worksheet’s tab name. The name must match, including spaces.
Change the data start row: Set firstDataRow to the first row that can contain records. A header in row 3, for example, means data might start on row 4.
Write a value instead of selecting: After determining the target range, assign a value to it, such as target.Value = "New item". This writes into the cell, so check that the target-selection logic is correct first.

If your data is an Excel Table, adding a row through the table’s data area is often preferable to selecting an arbitrary worksheet cell. Tables expand to include new records and can carry formulas and formatting into a new row. Make sure your macro’s target is actually within the table’s intended data area rather than merely somewhere below it.

Run the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, paste the code, and change the sheet and column references. Run the procedure from the editor, or use Excel’s macro controls if macros are enabled. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) to retain the code.

Excel for the web does not run VBA macros. Excel for Mac supports VBA, although keyboard shortcuts and the Visual Basic Editor access can vary with the keyboard and macOS settings; on many Mac keyboards, Option+F11 opens the editor, sometimes with the Fn key. Do not assume a Windows shortcut will work unchanged on a Mac. On either platform, use trusted macro files and keep a backup before running code that changes workbook contents.

Frequently Asked Questions

How do I find the next blank cell in column A with VBA?

To append below the last entry, find the last occupied cell with Cells(Rows.Count, "A").End(xlUp) and use the next row. Check for an entirely empty column first if the macro must handle that case.

How do I find the first empty cell in a column, not the last one?

Loop from the first data row downward and test each cell with IsEmpty. The first match is the first genuinely empty cell. This differs from appending after the last entry when the list contains gaps.

Does End(xlUp) count a formula that returns an empty string?

Yes. A formula is still present in the cell, even when its displayed result is blank, so it can be treated as the last occupied cell. Use a result-based test if your definition of blank is based on what appears on screen.

Why does my macro choose the wrong row?

Common causes include a formula farther down the column, choosing append logic when you meant to fill a gap, using the wrong starting row, or pointing at the wrong worksheet. Check the column’s formulas and confirm the header and data-row assumptions.

Can VBA find the next empty cell in Excel for the web?

No. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel app to use VBA, or consider an alternative such as Office Scripts if it suits your workbook and environment.

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.