VBA IsEmpty

VBA IsEmpty

IsEmpty is a function which is used to check whether the cell being referred to is empty or not. It is very similar to the ISBLANK function in excel. The IsEmpty function in Excel VBA is also called an information function in excel as it gives the information on whether the given cell is blank or not.

IsEmpty function is an inbuilt function in Excel VBA. As explained in the above definition it is used to determine whether the given cell is blank or not. If the given cell is empty we can display a message to the user that the cell is empty and if it is not empty we can display the message that the cell is not empty.

Syntax of IsEmpty in Excel VBA

The syntax for Isempty function is as follows:

We can use this function to check whether a single cell is empty or the whole range of data is empty or not. There are two values returned by this function. One is true while another one is false. If the given cell is blank the function returns the value as true and if the given cell is not blank it gives value as false.

How to Use VBA IsEmpty Function in Excel?

We will learn how to use a VBA IsEmpty function with few examples in excel.

VBA IsEmpty – Example #1

First, let us see the value returned by this function how it works.

Follow the below steps to use VBA IsEmpty function in Excel:

Step 1: Go to the developer’s tab and click on visual basic.

Step 2: Write the following code in the project.

Code:

Sub Check1()

Dim MyCheck As String
MyCheck = IsEmpty(Range("A1").Value)
MsgBox MyCheck

End Sub

First, let us understand the code written above step by step:

• Check is the name of the subfunction defined.
• Mycheck is the variable we have defined it as a string because the Isempty function returns a logical value.
• Mycheck stores the value of Isempty returned when it checks the cell A1.
• The value stored in Mycheck Variable is displayed by MsgBox function.

Step 3: Run the code by clicking the run button.

We can see that value returned by the function is true as cell A1 is empty.

VBA IsEmpty – Example #2

Now let us use Isempty function with if function to check for a certain cell in a worksheet whether it is blank or not.

Follow the below steps to use VBA IsEmpty function in Excel:

Step 1: In the developer’s tab click on Visual Basic under the code’s section.

Step 2: Write the following code in the code window,

Code:

Sub Sample1()
If IsEmpty(Range("A1")) = False Then

    MsgBox "Cell A1 is not empty"
Else

    MsgBox "Cell A1 is empty"
End If
End Sub

Let us again understand the code written above once again.

• First, we have defined our subfunction as Sample1.
• We nest Isempty function with If function to check whether cell A1 is empty or not.
• If cell A1 is empty we use msgbox function to display the message that the given cell is empty.
• If the cell A1 is not empty we use msgbox function to display the message that the given cell is not empty.

Step 3: Run the above code by clicking on the run button.

We see the result displayed as cell A1 is empty.

Step 4: Now put a random value in cell A, for example, I have put a value A in cell A1.

Step 5: Now run the code again and we get the following result.

VBA IsEmpty – Example #3

Now let’s use this function to find out whether the given range of cells is blank or not. Earlier we used this function in a single cell. In this example, our data range will be from B1:D7.

Follow the below steps to use VBA IsEmpty function in Excel:

Step 1: In the developer’s tab click on Visual Basic under the code’s section.

Step 2: Write the following code in the code window,

Code:

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

bIsEmpty = False
For Each cell In Range("B1:D7")
    If IsEmpty(cell) = True Then
        bIsEmpty = True
        Exit For
    End If
Next cell

If bIsEmpty = True Then

    MsgBox "empty cells"
Else

    MsgBox "cells have values!"
End If
End Sub

Let us understand the above-written code step by step.

• After defining the subfunction as Sample 2 we have defined a variable named cell as Range and B is empty as Boolean as Boolean stores logical values.
• We have predefined that Bisempty will be false if the cell range given is not empty.
• But if the given cell range is empty the Value stored in Bisempty will be true.
• If the value stored in Bisempty variable is true we display a message as Empty cells or if the value stored in the variable is false we display the message as Cells have values.

Step 3: Run the above code by clicking on the run button.

We see the following result displayed as cell A1 is empty.

Things to Remember

There are few things which we need to remember about Isempty Function in Excel VBA:

• Isempty is similar to Isblank function in Excel.
• IsEmpty is an information function.
• IsEmpty function returns a logical value i.e. true or false.
• IsEmpty function can be used of a single cell or for a range of cells.

Recommended Articles

This has been a guide to VBA IsEmpty. Here we discussed how to use Excel VBA IsEmpty Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Copy Paste Function in VBA
2. Substring Excel Function
3. VBA Subscript out of Range
4. Excel ISNUMBER Formula