VBA Find First Empty Cell in a Column

To find the first truly empty cell in column A with VBA, scan from the top and test each cell with IsEmpty. The macro below selects the first blank cell, including a blank between filled cells; change the column number to search another column.

What “first empty cell” means

There are two common meanings behind this question. You might want the earliest empty cell when reading down from row 1, or you might want the next available row after a list. Those are not always the same location. For example, if A1:A3 contain values, A4 is empty, and A5 contains a value, the first empty cell from the top is A4. The next row after the last occupied entry is A6.

The distinction matters because a column can contain gaps. Decide whether a gap should be used or preserved before choosing a method. The first macro in this article finds the first empty cell from the top. A later method shows how to append after the last used row instead.

VBA macro to find the first empty cell

This version searches column A, starting at row 1. It stops at the first cell whose value is genuinely empty and selects it. The worksheet must be the active worksheet when you run the macro.

Sub SelectFirstEmptyCellInColumn()
    Dim ws As Worksheet
    Dim targetColumn As Long
    Dim rowNumber As Long

    Set ws = ActiveSheet
    targetColumn = 1 ' Column A

    For rowNumber = 1 To ws.Rows.Count
        If IsEmpty(ws.Cells(rowNumber, targetColumn).Value2) Then
            ws.Cells(rowNumber, targetColumn).Select
            Exit Sub
        End If
    Next rowNumber

    MsgBox "The column has no empty cells."
End Sub

To search column B, set targetColumn = 2; column C is 3, and so on. The loop checks row 1 first, so if A1 is empty it selects A1 immediately. If A1:A10 contain values and A11 is blank, it selects A11. If there is a gap at A4, it selects A4 even if later rows contain data.

Excel worksheets have a fixed number of rows, so the loop is finite. In an unusually dense column, checking cells one at a time can take time. For a typical list that has a nearby empty cell, it will stop as soon as it finds one. If your goal is to add a record below existing data, the last-used-row approach is usually a better fit.

How the code works

ws stores a reference to the active worksheet, and targetColumn stores the column number to inspect. The For loop advances rowNumber from 1 through the worksheet’s row count. At each step, ws.Cells(rowNumber, targetColumn) refers to one cell at the intersection of that row and column.

Value2 reads the cell’s value without applying VBA’s date or currency conversions. IsEmpty returns True when the cell’s value is an uninitialized Empty value, as it is for a cell with no entered value or formula. When that test succeeds, the macro selects the cell and Exit Sub ends the procedure, so later blanks are not considered. The message at the end is reached only if every row in the column fails the test.

The code uses a numeric column index because it is simple to change and avoids needing to translate a letter into a number. If you want to make the worksheet explicit rather than depend on whichever sheet is active, replace the assignment with a workbook and worksheet reference, such as Set ws = ThisWorkbook.Worksheets("Data"). The sheet name must match an existing worksheet tab.

Find the next row after a list instead

When the aim is to append a new item beneath a list, you generally want the row after the last nonempty cell, not the first gap. This compact macro finds that row in column A. It treats a completely empty column as having its next row at row 1 and reports the rare case where the column is full.

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

    Set ws = ActiveSheet

    If Application.CountA(ws.Columns(1)) = 0 Then
        nextRow = 1
    Else
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        If lastRow = ws.Rows.Count Then
            MsgBox "There is no row below the last used row."
            Exit Sub
        End If
        nextRow = lastRow + 1
    End If

    ws.Cells(nextRow, 1).Select
End Sub

CountA checks whether column A contains anything. If it does, End(xlUp) starts at the bottom of the worksheet and moves upward to the last nonempty cell; adding 1 gives the row beneath it. This method intentionally skips internal gaps. If A1:A3 and A5 contain data, it selects A6, not A4.

Formulas count as contents for this purpose, including formulas that display an empty string. That can be useful when formula cells are part of the list structure, but it can also make the apparent last used row farther down than the visible data. Choose the first macro if the criterion is instead to find a cell with no value at all.

Empty cells, formulas, and displayed blanks

A cell that looks blank is not necessarily empty. A formula such as one that returns an empty string can display nothing while still being present in the cell. Because the first macro tests IsEmpty(cell.Value2), it will not treat a formula result of "" as an empty cell: the formula returns a zero-length string, not the Empty value used for a truly unused cell.

Other cases can also confuse a visual check. A cell may contain a space, an apostrophe entered to force text, or a nonprinting character. These cells have contents even if they appear blank. Formatting alone—such as a fill color or border—does not put a value in a cell, so IsEmpty still treats a formatting-only cell as empty.

If you deliberately want to treat a formula returning an empty string as available, use a different test and decide how to handle errors and spaces. For example, Len(CStr(cell.Value2)) = 0 considers an empty string and a genuinely empty value blank, but a cell containing spaces has a positive length. It can also raise an error if the cell contains an Excel error value, so production code should account for that case rather than assuming every cell can be converted to text.

Choose the right search behavior

Use the search that matches the structure of your worksheet. Before running a macro, check whether the first row is a heading and whether gaps are meaningful. If row 1 contains a label such as “Customer,” then searching from row 1 may return a blank in the heading area or select the label’s neighboring cell, depending on the column. Set the starting row to 2 when the data begins below a header.

Find the earliest gap: scan from the first data row downward and stop at the first genuinely empty cell. This can reuse a gap in a column with scattered records.
Add below all existing entries: find the last nonempty row and select the row after it. This preserves gaps and is often appropriate for appending records.
Ignore formulas that display blank: decide whether formulas are structural content. The supplied tests count a formula as content even when its displayed result is empty.
Work on a named sheet: assign ws to the intended worksheet rather than relying on the active sheet, especially when a workbook has several tabs.

For a different starting row, change the loop’s starting value. For example, use For rowNumber = 2 To ws.Rows.Count to skip a header in row 1. Keep in mind that this changes the meaning of “first”: the macro will not inspect any cell above row 2.

Run the macro safely in Excel

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure into the module, and return to Excel. Run it with the worksheet you want active by pressing Alt+F8, choosing the macro, and selecting Run. If the Developer tab is visible, you can also use Developer > Macros.

On a Mac, the Visual Basic Editor shortcut is commonly Option+F11 (on some keyboards you may also need the Fn key). You can open the macro list from Tools > Macro > Macros, or use the Developer tab if it is enabled. Menu names and shortcut behavior can vary with macOS keyboard settings. Excel for the web can open workbooks containing VBA but cannot create, run, or edit VBA macros; use the desktop Excel app for these steps.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the VBA code. Save a separate copy before running a macro that changes workbook data. The examples here only select a cell, but macros that write, clear, or rearrange data can have lasting effects: VBA actions cannot be undone with Ctrl+Z. Enable macros only in workbooks you trust.

Common problems and fixes

The macro selects a gap instead of the bottom: that is the intended behavior of the top-down scan. Use the next-row macro if you want to append after the last entry.
A cell that looks blank is skipped: check the formula bar for a formula, a space, or another hidden character. The supplied first-empty macro tests whether the cell has a value, not whether it looks blank on screen.
The wrong worksheet is searched: ActiveSheet means the worksheet currently selected when the macro runs. Set ws to the specific worksheet by name to avoid relying on selection.
The macro will not run: confirm that the code is in a standard module, that macros are permitted by your Excel security settings, and that you are using desktop Excel rather than Excel for the web.
The result is one row too low: confirm whether the first data row is row 1 or row 2 and whether you used the top-down or append method. The two procedures answer different questions.

Frequently Asked Questions

How do I find the first blank cell in column B?
In the first macro, change targetColumn = 1 to targetColumn = 2. The numeric index 2 refers to column B. The macro still searches from row 1 and stops at the earliest empty cell.

How do I start searching at row 2?
Change the loop to For rowNumber = 2 To ws.Rows.Count. This skips row 1, which is useful when it contains a heading. The first blank found will then be the earliest one at or below row 2.

Does IsEmpty count a formula returning an empty string as blank?
No. A formula is still present even if its displayed result is an empty string, so IsEmpty returns False for that cell’s value. Use a test based on the returned string length only if that behavior is wanted, and handle error values appropriately.

How do I find the first empty cell after the last used row?
Use the second macro. It finds the last nonempty cell in column A and selects the following row, so internal blanks are left alone. Change the column index from 1 if your list is in another column.

Can I use this VBA in Excel for the web?
No. Excel for the web does not run or edit VBA macros. Open the workbook in the desktop Excel app for Windows or Mac to use the Visual Basic Editor and run the procedures.

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.