VBA Check IF a Cell is Empty + Multiple Cells

Use VBA to Check IF a Cell is Empty

1. Start with the function name “IsEmpty”.
2. Specify the cell that you want to check.
3. Use a message box or a cell to get the result value.
4. In the end, run the code.

MsgBox IsEmpty(Range("A1"))

In most cases, you will use IsEmpty with an IF statement, so the code does one thing when the cell is empty and another when it has a value:

Sub check_cell_empty()
    If IsEmpty(Range("A1")) Then
        MsgBox "Cell A1 is empty."
    Else
        MsgBox "Cell A1 has a value."
    End If
End Sub

And to check if a cell is not empty, add Not before IsEmpty, like If Not IsEmpty(Range(“A1”)) Then. You can also use ActiveCell instead of Range(“A1”) to check the cell that is selected.

Check IF Multiple Cells Are Empty

If you want to check and count the empty cells in a range, you need to loop through each cell in the range.

Sub vba_check_empty_cells()

Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range

Set myRange = Range("A1:A10")

For Each myCell In myRange
    c = c + 1   
    If IsEmpty(myCell) Then
        i = i + 1
    End If   
Next myCell   

MsgBox _
"There are total " & i & " empty cell(s) out of " & c & "."

End Sub

The above code loops through each cell in the range A1:A10 and checks each cell individually using the ISEMPTY function to determine whether it’s empty or not. It counts each empty cell and, in the end, shows a message box with the total number of cells and empty cells.

Use the following code if you want to highlight empty cells as well.

Sub vba_highlight_empty_cells()

Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range

Set myRange = Range("A1:A10")

For Each myCell In myRange '
    c = c + 1
    If IsEmpty(myCell) Then
        myCell.Interior.Color = RGB(255, 87, 87)
        i = i + 1
    End If
Next myCell

MsgBox _
"There are total " & i & " empty cell(s) out of " & c & "."

End Sub

Check IF an Entire Range Is Empty

If you only want to know whether the whole range is empty, you don’t need a loop. Use the worksheet function COUNTA, which counts the cells that are not empty. If it returns 0, all the cells in the range are empty.

Sub check_range_empty()
    If WorksheetFunction.CountA(Range("A1:A10")) = 0 Then
        MsgBox "All the cells are empty."
    Else
        MsgBox "The range has at least one value."
    End If
End Sub

To select or work with all the empty cells of a range in one go, you can also use the Special Cells method with xlCellTypeBlanks. Just note that it gives the “No cells were found” error (1004) when the range has no empty cells.

Cells That Look Empty but Are Not

IsEmpty returns true only when there is nothing in the cell. So, it returns false for a cell that looks empty but has:

• A formula that returns a blank: like a formula that shows “” when there’s no value.
• Only spaces: a space typed into the cell or left over from imported data.

To treat these cells as empty as well, check the length of the value after removing the spaces with Trim:

Sub check_cell_looks_empty()
    If Len(Trim(Range("A1").Value)) = 0 Then
        MsgBox "Cell A1 is empty or has only spaces."
    End If
End Sub

The same goes for COUNTA: it counts a cell with a formula that returns “” as not empty.

Frequently Asked Questions