Excel VBA: Find the First Empty Cell in a Column

To find the first empty cell in an Excel column with VBA, start at the first row you want to check and test each cell with IsEmpty; stop at the first match. The macro below searches column A from row 2, so it skips a header and finds a gap even when later cells contain data.

How the search works

A column can contain empty cells between populated cells, so the first blank is not necessarily the cell after the last entry. For example, if A2, A3, and A5 have values but A4 is blank, the first empty cell in the data area is A4. A search that jumps directly to the bottom of the data and moves one row down would return A6 instead.

The macro searches from the top of the chosen data area toward the last occupied cell. If it reaches that last occupied cell without finding a gap, it checks the next row, which is then the first empty cell after the existing data. This avoids scanning all 1,048,576 rows when the column contains only a modest amount of data.

VBA macro to select the first empty cell

In this example, the worksheet is named Sheet1, column A is the search column, and row 1 contains a heading. Change the worksheet name, column, or starting row to match your workbook.

Sub SelectFirstEmptyCell()
    Dim ws As Worksheet
    Dim columnNumber As Long
    Dim firstDataRow As Long
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim candidateRow As Long

    ' Set the worksheet and search settings.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    columnNumber = ws.Range("A1").Column
    firstDataRow = 2

    ' Find the last cell in this column that Excel treats as occupied.
    lastRow = ws.Cells(ws.Rows.Count, columnNumber).End(xlUp).Row

    ' Check for the first genuinely empty cell within the data area.
    For rowNumber = firstDataRow To lastRow
        If IsEmpty(ws.Cells(rowNumber, columnNumber).Value2) Then
            ws.Activate
            ws.Cells(rowNumber, columnNumber).Select
            Exit Sub
        End If
    Next rowNumber

    ' If there was no gap, use the row just below the last occupied cell.
    If lastRow < ws.Rows.Count Then
        candidateRow = lastRow + 1
        If candidateRow < firstDataRow Then candidateRow = firstDataRow
        ws.Activate
        ws.Cells(candidateRow, columnNumber).Select
    Else
        MsgBox "No empty cell was found in the search area."
    End If
End Sub

The loop tests cells in order and exits as soon as it finds one, so it does not continue through the rest of the column. The final section handles the case where all rows from the starting row through the last occupied row are filled. If the entire remaining column is occupied, it displays a message instead of trying to select a row beyond Excel’s limit.

Set the column and starting row

Use columnNumber = ws.Range("A1").Column for column A, or change A1 to a cell in another column, such as C1. The row number in that reference does not determine where the search begins; the separate firstDataRow setting does that. For a table with headers in row 4 and data beginning in row 5, set firstDataRow = 5.

If the search should include the heading row, set the starting row to 1. Be cautious about doing this: a blank heading cell may be returned even if the records below it are complete. Similarly, if the intended search area is only rows 2 through 500, add an explicit upper limit rather than searching to the last used cell in the whole column. A bounded search can be useful when cells below the table contain notes or unrelated content.

Use the worksheet’s exact tab name in the Worksheets reference.
Use a column reference such as ws.Range("D1").Column to make the target column clear.
Set the first data row to the first row that should count as part of the search.
If the macro should search a different workbook, qualify the worksheet reference with that workbook rather than assuming the active sheet is the right one.

What Excel considers empty

IsEmpty identifies a cell whose value is genuinely empty. A cell containing a formula is not empty, even when that formula displays an empty string. For instance, a formula such as =IF(B2=0,"",B2) can make a cell look blank while the cell still contains a formula. The macro therefore skips it.

This distinction matters when the goal is to find the next place to enter a value: overwriting a formula may be undesirable, so treating formula cells as occupied is often correct. If the goal is instead to find the first cell that looks blank on screen, the test needs different logic. A formula that returns an empty string may be treated as visually blank, and a cell containing only spaces may also look blank even though it has text. Decide which meaning of blank fits the task before changing the condition.

Errors also need an explicit policy in a visual-blank search. Trying to convert an error value to text can cause a VBA type mismatch. A robust version should first check IsError and treat error cells as occupied, then check the text length of non-error values. Trimming spaces is a further choice: it treats cells containing only ordinary leading or trailing spaces as blank, but it will not necessarily remove every special whitespace character.

Finding the next row after the data instead

A common related task is appending a new record after the last used cell, not filling the first gap. These are different searches. If A2:A5 contains values and A3 is blank, the first-empty-cell macro selects A3; an append operation normally selects A6 so that the existing records stay in their current rows.

The expression Cells(Rows.Count, columnNumber).End(xlUp).Row + 1 is a common way to calculate the row after the last nonempty cell in a column. It moves upward from the bottom of the worksheet to the last occupied cell, then adds one. It does not search for internal gaps. Choose it for appending, not when the requirement is to fill the earliest blank in a list.

Be aware that the definition of “last used” depends on what Excel finds in the selected column. A formula counts as occupied even if its displayed result is empty. Formatting by itself generally does not make a cell a data entry for this upward search, but formulas, constants, and other content in the target column can affect the result. If the column contains unrelated entries below the table, a table-specific boundary is safer than relying on the entire worksheet column.

Run the macro safely

VBA macros run in the desktop version of Excel. Excel for the web cannot run VBA macros; open the workbook in the desktop app to use this code. On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the code, and edit the settings. You can run it from the editor, or use Developer > Macros, select SelectFirstEmptyCell, and choose Run. If the Developer tab is not visible, it can be enabled in Excel Options under Customize Ribbon.

On Mac, Excel also supports VBA in the desktop app, but keyboard shortcuts can depend on the keyboard and function-key settings; use the Developer tab or the Visual Basic Editor menu if the Windows shortcut does not apply. The web app is not a substitute for running the macro. Save a working copy of the workbook before running VBA. This particular macro selects a cell rather than changing data, but macros can perform actions that cannot be reversed with Ctrl+Z, and the undo history is cleared when a macro runs.

If you save the workbook with its macro, use an Excel Macro-Enabled Workbook (.xlsm) file. A standard .xlsx file does not preserve VBA code. Only enable macros in files you trust, and follow your organization’s security rules for macro-enabled workbooks.

Common problems and practical checks

The macro selects the wrong blank: Check firstDataRow and confirm that the selected column is the one containing the records. A blank in a header or notes area can be the first result if it falls inside the chosen search range.
It skips a cell that looks empty: The cell may contain a formula returning an empty string, a space, or another value. IsEmpty tests the underlying value, not the appearance of the cell.
It selects the next row instead of a gap: Confirm that the gap is in the column being searched. The macro checks only one column; a row that is blank across several columns is a separate condition.
It cannot find the worksheet: The text in Worksheets("Sheet1") must match the worksheet tab name. If the sheet is hidden, selecting it may also fail until it is made visible.
It errors on a protected sheet: Sheet protection can prevent selecting certain cells. Check the protection settings or use a permitted range; do not remove protection unless you are authorized to do so.

For a large worksheet, stopping at the first match is efficient when the blank is near the top. If the column is densely filled and the first gap is very far down, the code still checks each row in sequence. That is usually straightforward and predictable; more elaborate methods can be worthwhile for very large datasets, but they must preserve the intended definition of blank and account for formulas.

Frequently Asked Questions

How do I find the first blank cell in column A starting at row 2?
Set the worksheet to the correct tab, use column A, and set firstDataRow = 2. The macro above uses those settings and selects the first genuinely empty cell it encounters.

Does this find the first empty cell or the next row after the data?
It finds the first empty cell from the specified starting row, including gaps inside the data. The next-row-after-data method finds the last occupied row and adds one, so it can skip earlier gaps.

Will a formula that displays a blank count as empty?
No. IsEmpty treats a formula cell as occupied even if the formula returns an empty string. Use a separate, error-aware test if you want to classify cells by their displayed value instead.

How can I search a different column?
Change the column reference, for example from ws.Range("A1").Column to ws.Range("C1").Column. Keep the starting row aligned with the first row of the data you want to inspect.

Can I run this macro in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac, and save it as an .xlsm file if you need to retain the macro.

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.