Excel VBA Find a Blank Cell: Methods and Examples

To find the first truly empty cell in a range with Excel VBA, loop through the cells and test IsEmpty(cell.Value). To find the next open row in a data column, use End(xlUp) and add one, while handling the case where the column has no data. The right method depends on whether you mean a cell that has never contained a value, a formula that displays nothing, or the next row for new data.

What “blank cell” means in VBA

Excel can make several different cells look blank. A cell may be genuinely empty, contain a formula whose result is an empty string, contain spaces, or have formatting but no value. These cases can look alike on the worksheet but behave differently in VBA. Decide what should count as blank before choosing a test.

Genuinely empty: The cell has no value or formula. IsEmpty(cell.Value) returns True for this case.
Formula returning an empty string: The cell contains a formula, such as one using IF, that displays no characters. The cell is not truly empty, so IsEmpty returns False.
Spaces or other characters: A cell containing a space is not empty, even if it is difficult to see. A test for a zero-length string will not treat a space as blank unless you trim it.
Formatting only: A fill color, border, or number format does not make a cell nonempty for value-based tests.

This distinction matters for tasks such as filling a missing input, finding the next record row, or checking whether a formula has produced a result. A macro that treats a formula-generated blank as an available cell could overwrite the formula.

Choose a method for the task

For a bounded area, such as A2:A100, a loop is straightforward and lets you define exactly what “blank” means. For appending a record beneath a list, finding the last occupied cell in a key column is usually more efficient than examining every cell in the worksheet. For an existing range with scattered gaps, a loop can locate the first gap or process each gap in turn.

Before writing code, identify the worksheet and range explicitly. Relying on whichever sheet happens to be active can make a macro inspect or change the wrong place. In the examples below, the worksheet is named Data; replace that name and the column or range to match your workbook.

Find the first truly empty cell in a range

This macro checks A2:A100 from top to bottom and reports the address of the first cell that contains no value or formula. It does not change the worksheet. If every cell in the range contains something, it reports that no blank was found.

Save a copy of the workbook before running any macro. VBA actions cannot be undone with Ctrl+Z, even when a macro appears to make only a small change.

Sub FindFirstTrulyEmptyCell()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range

    ' Set the worksheet and the cells to inspect.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")

    For Each cell In searchRange.Cells
        If IsEmpty(cell.Value) Then
            MsgBox "First empty cell: " & cell.Address(False, False)
            Exit Sub
        End If
    Next cell

    MsgBox "No truly empty cell was found in " & searchRange.Address(False, False)
End Sub

Address(False, False) returns a relative-style address such as A7 rather than $A$7. The loop stops as soon as it finds the first match, so the order of the range determines which blank it reports. If the range is A2:A100, it starts at A2; if you want a different search order, define the range accordingly.

Find the next open row in a column

When a list uses column A as its key column, the usual goal is to find the row immediately after its last occupied cell. The next macro handles both a populated column and a completely empty one, returning row 1 when there is no data in column A. It displays the row number rather than inserting or changing anything.

Save a copy before running a macro, because VBA changes cannot be reversed with Ctrl+Z. The code treats formulas as occupied cells, including formulas that currently display an empty string.

Sub FindNextOpenRow()
    Dim ws As Worksheet
    Dim nextRow As Long

    ' Set the worksheet whose column A holds the list.
    Set ws = ThisWorkbook.Worksheets("Data")

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

    MsgBox "Next open row: " & nextRow
End Sub

CountA prevents a common edge-case error: when a column is entirely empty, End(xlUp) from the bottom of the sheet reaches the top cell, and simply adding one would return row 2. If your table has headers in row 1 and new records must begin no earlier than row 2, change the empty-column result to 2. If records are identified by another column, use that column consistently instead of A.

Include formulas that display as blank

If a formula returning an empty string should count as blank, do not use IsEmpty alone. The following test checks the cell’s value length and first excludes error values, which cannot safely be converted to ordinary text. It treats a genuinely empty cell and a formula result of an empty string as blank, but it does not treat a cell containing spaces as blank.

Save a copy of the workbook before running a macro. This example reports the first matching cell in A2:A100 and does not overwrite it.

Sub FindFirstDisplayedBlank()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range

    ' Set the worksheet and search area.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")

    For Each cell In searchRange.Cells
        If Not IsError(cell.Value2) Then
            If Len(CStr(cell.Value2)) = 0 Then
                MsgBox "First blank-looking cell: " & cell.Address(False, False)
                Exit Sub
            End If
        End If
    Next cell

    MsgBox "No blank-looking cell was found."
End Sub

Value2 reads the cell value without applying Excel’s currency or date formatting. The test is about the underlying result, not how that result is formatted. To count cells containing only ordinary spaces as blank too, use Len(Trim$(CStr(cell.Value2))) = 0 inside the error check. Trimming can change the meaning of data, however, so use it only if whitespace should genuinely be ignored.

Find gaps versus append below the last entry

Finding the first blank in a fixed range and finding the next row after a list are different operations. Suppose A2, A3, and A5 contain entries, while A4 is empty. A top-to-bottom search for the first blank returns A4. The next-row method based on the last occupied cell returns row 6, because it appends after A5. Neither answer is wrong; they answer different questions.

Use a range loop when gaps matter and you want to fill the first available gap.
Use the last-used-row approach when new records should be appended after existing data, leaving earlier gaps untouched.
Choose a reliable key column for append logic. If column A is sometimes empty for valid records, the last row in A may not represent the last row of the dataset.
Use a bounded range when possible for a search that should stop at a known limit. Searching an entire worksheet column one cell at a time can be needlessly slow.

For multi-column tables, you may need to determine the last row from a column that is filled in every record, or inspect the table’s data range. A single-column check cannot infer that a row is unused if the chosen column is intentionally blank.

Common problems and how to avoid them

One frequent mistake is searching the active sheet unintentionally. A statement such as Cells(Rows.Count, 1) depends on the active worksheet context, which can become confusing in longer macros. Referencing ws.Cells and ws.Rows.Count, as in the examples, ties the operation to a specific worksheet.

Another issue is treating formatting as evidence of data. A formatted cell may look occupied, but IsEmpty can still return True if it has no value or formula. Conversely, a formula that displays nothing is still present. Use the test that matches the job, and consider inspecting the formula bar when a cell’s appearance is misleading.

Also check for merged cells and protected sheets. A merged area does not behave like a regular grid of independent cells; its value is associated with the upper-left cell of the merged area. A protected worksheet may prevent a later macro from writing into a found blank cell, even though reading the cell works. If a macro only reports an address, protection is less likely to matter; if it writes data, confirm that the target is editable.

Run and protect VBA code in Excel

These macros are intended for desktop Excel for Microsoft 365 on Windows. To use one, open the workbook, choose Developer > Visual Basic, insert a standard module with Insert > Module, and paste the code into it. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. Run a macro from the Visual Basic Editor or through Developer > Macros.

Save a workbook containing VBA in an Excel Macro-Enabled Workbook (.xlsm) format so that the code is retained. Excel for the web can open and edit many workbooks, but it cannot create, run, or edit VBA macros; use the desktop application for these examples. Excel for Mac supports VBA in the desktop application, though ribbon and keyboard details can differ from Windows. The worksheet references and core VBA logic shown here are not Windows-only.

Only enable macros in workbooks whose source you trust. A macro can make broad changes, and Excel’s Undo command does not reverse VBA actions. Test code on a copy or a small sample range first, especially before adapting a reporting example to write values, delete cells, or alter a large dataset.

Frequently Asked Questions

How do I find the first blank cell in a column with VBA?
Loop through a defined range in that column and test each cell with IsEmpty if only genuinely empty cells should match. Set the range’s starting and ending rows to control where the search takes place.

Does IsEmpty detect a formula that returns an empty string?
No. The cell still contains a formula, so IsEmpty returns False. Check the evaluated value’s length instead if a formula result of zero characters should count as blank.

How do I find the next empty row after a list?
Find the last occupied cell from the bottom of a reliable key column with End(xlUp), then add one. Handle an entirely empty column separately, and use a column that is populated for every record.

Why does my macro return row 2 when the column is empty?
Starting at the bottom and using End(xlUp) reaches row 1 when the column has no entries. Adding one gives row 2, so test whether the column has any values first and choose the desired starting row explicitly.

Can VBA find a blank cell in Excel for the web?
Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run VBA; if you need browser-based automation, investigate Office Scripts instead, which is a separate feature and uses a different programming model.

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.