How to Check if a Cell Is Empty in Microsoft Excel VBA

1. Ways to Check if a Cell Is Empty
2. Method 1: The IsEmpty Function
3. Method 2: Compare With vbNullString
4. Method 3: Compare With an Empty String
5. Method 4: The Len Function
6. Method 5: The Count and Counta Functions
7. Method 6: The IsNull Function
8. Conclusion

VBA is a programming language for Microsoft Excel applications that provides users with various functions and features. In this article, we will be learning about how to check if a cell is empty.

Ways to Check if a Cell Is Empty

In MS Excel, a cell is a block identified as the intersection of a row and a column, having the row number and column letter as the identifier. These cells can hold various types of data.

However, we can also have an empty cell, which refers to one that holds no data.

In MS Excel, there can be many situations where we have to check if the cell contains any data or not. To assist us in doing this, MS Excel VBA provides multiple methods to check if a cell is empty.

These methods are explained in detail below, along with VBA code examples to help you understand them better.

Method 1: The IsEmpty Function

The IsEmpty function in Excel VBA is a built-in function that returns a Boolean value. This function can also be used along with cells to check if a variable is uninitialized.

It has the following syntax:

IsEmpty(value)

Here, value is the cell or variable you want to check.

If the cell is empty or the variable is uninitialized, this function returns True. Otherwise, it returns False.

Note: If you want to check if a value is blank in a worksheet cell, you can also use the IsBlank(value) worksheet function in the formula bar. However, you cannot use the IsBlank function in VBA as an alternative to the IsEmpty function.

Using the code below, let us test the IsEmpty function on some simple cells. We will also use the same two cells in the subsequent examples.

Sub ExampleIsEmpty()

If IsEmpty(Range("A1")) = True Then
MsgBox "Cell A1 is empty"
Else
MsgBox "Cell A1 is not empty"
End If

If IsEmpty(Range("A2").Value) = True Then
MsgBox "Cell A2 is empty"
Else
MsgBox "Cell A2 is not empty"
End If

End Sub

Running this code will give the following output in message boxes:

Note how we have used both IsEmpty(Range("A1")) and IsEmpty(Range("A2").Value). Both will work fine.

Method 2: Compare With vbNullString

If we want to check if a cell does not contain any value, we can also compare it with vbNullString. vbNullString is a constant which holds the Null value for strings.

If the comparison shows that the value in the cell is equal to vbNullString, we can conclude that the cell is empty.

The following code demonstrates how we can use the vbNullString constant to check for empty cells.

Sub ExamplevbNullString()

If Range("A1").Value = vbNullString Then
MsgBox "Cell A1 is empty"
Else
MsgBox "Cell A1 is not empty"
End If

If Range("A2").Value = vbNullString Then
MsgBox "Cell A2 is empty"
Else
MsgBox "Cell A2 is not empty"
End If

End Sub

Again, we can see that the output prompts the following message boxes:

Note: This method will not work when the cell does not have a value but contains an error like #N/A!. Therefore, it is safer to use the IsEmpty function.

Method 3: Compare With an Empty String

A similar method is to compare the value present in the cell with an empty string, which is defined in code as "". If the comparison returns True, the cell is empty and non-empty otherwise.

Here is an example of that which uses comparison with "" to determine if the cell is empty.

Sub ExampleEmptyString()

If Range("A1").Value = "" Then
MsgBox "Cell A1 is empty"
Else
MsgBox "Cell A1 is not empty"
End If

If Range("A2").Value = "" Then
MsgBox "Cell A2 is empty"
Else
MsgBox "Cell A2 is not empty"
End If

End Sub

The output will be as follows:

Method 4: The Len Function

To check if a cell is empty, we can also compute the length of the data in there and compare if it is equal to zero.

If the length is equal to zero, we can say that the cell is empty, implying that it contains no data. Otherwise, we say that the cell is not empty.

We use the VBA built-in Len function to compute the length of the data. It has the following syntax:

Len(value)

Here, value is the value of the cell you want to check.

Let us look at an example code that uses the Len function to determine if a cell is empty.

Sub ExampleLen()

If Len(Range("A1").Value) = 0 Then
MsgBox "Cell A1 is empty"
Else
MsgBox "Cell A1 is not empty"
End If

If Len(Range("A2").Value) = 0 Then
MsgBox "Cell A2 is empty"
Else
MsgBox "Cell A2 is not empty"
End If

End Sub

As expected, we get the following output:

Method 5: The Count and Counta Functions

The Count and Counta functions are worksheet functions in VBA that return the number of non-empty cells in the given range. The difference between them is that the Count function only counts numeric values, whereas the Counta function can work on any data.

We can utilize these functions to check if a given cell is empty. If the count is returned as 0, the cell is empty since the function counts only non-empty cells.

It is better to use the Counta function instead of the Count function because the Count function will consider textual or string-type data as blank.

The following code demonstrates the usage of the Counta function to check for empty cells.

Sub ExampleCounta()

If Application.WorksheetFunction.CountA(Range("A1")) = 0 Then
MsgBox "Cell A1 is empty"
Else
MsgBox "Cell A1 is not empty"
End If

If Application.WorksheetFunction.CountA(Range("A2")) = 0 Then
MsgBox "Cell A2 is empty"
Else
MsgBox "Cell A2 is not empty"
End If

End Sub

The output is as expected.

Method 6: The IsNull Function

Another method to check if a cell contains a Null value is to use the IsNull function. This is also a built-in function in VBA that returns a Boolean value: True if the value is equal to Null and False otherwise.

If this function returns True for the value of a cell, we can conclude that the cell is empty.

The syntax for the IsNull function is as follows:

IsNull(value)

Here, value is the value of the cell you want to check.

However, a point to be noted is that the Null value is not inherently allocated to a cell. Therefore, in many cases, this method will not work and will say that a cell is not empty even when it is blank because its value is not equal to NULL.

Conclusion

In this article, we have explained six methods to check if a cell is empty in Excel VBA:

1. The IsEmpty function
2. Comparison with vbNullString
3. Comparison with an empty string
4. The Len function
5. The Count and Counta functions
6. The IsNull function

We hope you were able to grasp these concepts. Keep learning!