Different Ways to Check if a Cell Is Empty in Microsoft Excel VBA
 Ways to Check if a Cell Is Empty

Method 1: The
IsEmpty
Function 
Method 2: Compare With
vbNullString
 Method 3: Compare With an Empty String

Method 4: The
Len
Function 
Method 5: The
Count
andCounta
Functions 
Method 6: The
IsNull
Function  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 builtin 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 theIsBlank
function in VBA as an alternative to theIsEmpty
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"))
andIsEmpty(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 theIsEmpty
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 nonempty 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 builtin 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 nonempty 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 nonempty cells.
It is better to use the Counta
function instead of the Count
function because the Count
function will consider textual or stringtype 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 builtin 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:
 The
IsEmpty
function  Comparison with
vbNullString
 Comparison with an empty string
 The
Len
function  The
Count
andCounta
functions  The
IsNull
function
We hope you were able to grasp these concepts. Keep learning!
Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!
GitHub