EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Information Functions VBA IsEmpty
Secondary Sidebar
VBA Information Functions
  • VBA Information
    • VBA Rename Sheet
    • VBA Worksheets
    • VBA IsEmpty
    • VBA Active Cell
    • VBA Workbook
    • VBA ISNULL
    • VBA Activate Sheet
    • VBA Cells
    • VBA Workbook Open
    • VBA Protect Sheet
    • VBA Unprotect Sheet
    • VBA Delete Sheet
    • VBA Editor
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Environ

VBA IsEmpty

By Madhuri ThakurMadhuri Thakur

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,389 ratings)

Syntax of VBA IsEmpty

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.

You can download this VBA IsEmpty Excel Template here – VBA IsEmpty Excel Template

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.

VBA IsEmpty Example 1-1

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

VBA IsEmpty Example 1-2

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.

Result of Example 1-3

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.

VBA IsEmpty Example 2-1

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

VBA IsEmpty Example 2-2

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.

Result of Example 2-3

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.

VBA IsEmpty Example 2-4

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

Result of Example 2-5

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.

VBA IsEmpty Example 3-1

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

VBA IsEmpty Example 3-2

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.

Result of Example 3-3

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. VBA Copy Paste
  2. VBA XML
  3. VBA Subscript out of Range
  4. VBA IsError
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course
4 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more