VBA check if cell is empty

The simplest way in VBA to check whether the cell is empty is by using the IsEmpty function.

Take a look at the following example.

In the column A you have some data. In cell A2 you have space. The following code takes the active cell and displays a message window to show you which cell is empty.

Sub CheckIfEmpty()
    If IsEmpty(ActiveCell.Value) Then
        MsgBox ("Cell is empty")
    Else
        MsgBox ("Cell is not empty")
    End If
End Sub

As you can see space is treated as not empty and formatting is treated as empty.

Treat the space as an empty cell

It’s quite likely that we want to treat space as an empty cell. This time we will write our code a little differently. We won’t use the IsEmpty function.

Sub CheckIfEmpty()
    Dim check_if_empty As String
    check_if_empty = Trim(ActiveCell.Value)
    If check_if_empty = "" Then
        MsgBox ("Cell is empty")
    Else
        MsgBox ("Cell is not empty")
    End If
End Sub

Code explanation

3. Trim(ActiveCell.Value) will remove multiple spaces from the beginning and end of the string. If there are only multiple spaces in a cell it will return “”.

Sub CheckIfEmpty()
    Dim check_if_empty As String
    check_if_empty = Trim(ActiveCell.Value)
    If check_if_empty = "" Then
        MsgBox ("Cell is empty")
    Else
        MsgBox ("Cell is not empty")
    End If
End Sub

Now if you run the code you will get.

Cell A2 is empty.

Cell A3 has a single space.

Cell A4 has multiple spaces.

Cell A5 has applied formatting.

Posted in vba