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 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.
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 6 7 8 9 |
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 “”.
|
1 2 3 4 5 6 7 8 9 |
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.