Use IsEmpty VBA to Check if Cell is Blank

Introduction to IsEmpty | IsEmpty Examples | Final Thoughts

Introduction to IsEmpty

The IsEmpty VBA function can be used to check if a cell is blank, just like the Excel ISBLANK worksheet function. But, there’s more!

Not only can IsEmpty check if a cell or range is empty, it can check if a variable is uninitialized. In other words, you can use it to see if your macro has assigned a value to a variable.

We’ll get to some examples shortly, but first, let’s start this tutorial with some IsEmpty Basics.

IsEmpty accepts one argument:

IsEmpty(Expression)

It gives you your result as a Logical Boolean. Recall, a Logical Boolean returns either True or False.

The “Expression” argument IsEmpty accepts is a Variant, so it can be pretty much anything - a variable, an object, a range and more.

As I mentioned in my IsNumeric VBA Tutorial, it’s a good practice to check if your cells are empty before applying certain VBA functions.

Let’s take a look at some examples to see what I mean.

IsEmpty Examples

Check if a Cell is Blank

Sub IsEmptyExample1()
If IsEmpty(Range("A2")) = False Then
    'Cell A2 is not blank
    MsgBox "Cell A2 is not empty"
Else
    'Cell A2 is blank
    MsgBox "Cell A2 is empty"
End If
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

This is a basic macro that checks whether or not you have entered content in cell A2. You should use IsEmpty If statements like this one near the top of your macros to protect against errors.

Not recognizing that a required cell is empty is one of the most common causes of VBA runtime errors and undesirable outcomes.

Capturing empty cells gives you control over your macro and lets you handle these exceptions.

Check if All Cells in a Range are Blank

Sub IsEmptyRange()
Dim cell As Range
Dim bIsEmpty As Boolean

bIsEmpty = False
For Each cell In Range("A1:B5")
    If IsEmpty(cell) = True Then
        'An empty cell was found. Exit loop
        bIsEmpty = True
        Exit For
    End If
Next cell

If bIsEmpty = True Then
    'There are empty cells in your range
    '**PLACE CODE HERE**
    MsgBox "There are empty cells in your range"
Else
    'There are NO empty cells in your range
    '**PLACE CODE HERE**
    MsgBox "All cells have values!"
End If
End Sub

The comments in this VBA example explain what’s going on. This macro checks all the values in range A1:B5 and looks for empty cells. If one is found, the macro recognizes it and gives you the opportunity to program two different flow paths for the two scenarios.

Check if a Variable is Initialized

Sub IsEmptyExample2()
Dim str1 As Variant
MsgBox IsEmpty(str1) 'Returns True
str1 = "Hello there!"
MsgBox IsEmpty(str1) 'Returns False
str1 = Empty
MsgBox IsEmpty(str1) 'Returns True
End Sub

The first MsgBox returns True because the variable str1 has not been initialized - it’s empty. In other words, even though it’s been declared a variable of type Variant, it hasn’t been assigned a value.

Once you assign it the value of Hello there!, the variable is no longer empty so the IsEmpty function returns False. It’s been initialized!

Only when you set the variant back to Empty will the IsEmpty command once again return True. That’s what we did right before our 3rd MsgBox.

Now it’s time for the shocker. This doesn’t work for all data types! It only works for the Variant in this example because the default value for a variant is Empty. If we declare the variable as a string, the IsEmpty function will always return False. That’s because the default value for a string is the empty string "". It comes pre-initialized.

An empty string is not the same as an uninitialized variable. An empty string is NOT considered empty, as defined by the IsEmpty function. Try it yourself:

Sub IsEmptyExample3()
Dim str1 As String
MsgBox IsEmpty(str1) 'Returns False
str1 = "Hello there!"
MsgBox IsEmpty(str1) 'Returns False
str1 = Empty
MsgBox IsEmpty(str1) 'Returns False
End Sub

You can prove the default value is an empty string by right clicking str1 and clicking “Add Watch.” By stepping into your macro (pressing the F8 button once), you can see see the default value is "". Keep running your macro, and you’ll see all three MsgBoxes return False. VBA is a funny thing, like that.

Final Thoughts

Although it can be used to check whether or not variables have been initialized, the most common use of IsEmpty is to check for blank cells. By learning these examples, you can now perform IsEmpty checks for individual cells, and for entire ranges!

Remember, when applied to a cell, the VBA function IsEmpty is the same as the Excel Worksheet Function ISBLANK. If you’re familiar with using =ISBLANK() in your worksheet cells, learning the IsEmpty VBA function will be a breeze.

I know you want to learn more VBA. To see more VBA tips, tricks and techniques, subscribe using the form below.