Detect If Range Is Empty in VBA

Just as with everything in Excel, there is always a way to simplify and make your life easier with Visual Basic for Applications (VBA).

In the example below, we will show you how to detect if a certain cell or range is empty in our worksheet with the help of VBA.

Detect If the Cell Is Empty in VBA

The first thing we need to do is define the range that we will use for our example. We will use the table of NBA players that will contain their names, clubs, conferences, and statistical data from several categories:

Table

Description automatically generated

As seen, there are some fields in our table that are empty.

To create our code, we first need to click CTRL + F11 on our keyboard, open the VBA, and then go to the Insert tab, and find the Module:

Graphical user interface, application

Description automatically generated

To check if a certain cell is empty or not, we will insert a simple code:

IsEmpty returns a Boolean value, so either TRUE or FALSE. In this case, if the cell is empty, it will return the value TRUE, and the message that the value is empty. Since cell A1 is populated in our example, it will return the following message:

Table

Description automatically generated

Similarly, we can check if any of the cells in our range is empty. We just need to change the cell that we refer to.

Detect If the Range is Empty

There are multiple ways in which we can find out if our range is empty. How we can set up our formula depends a lot on our concept of emptiness. Does the empty range mean that we do not have any cells in the range, or do we just lack some cells?

For our example, we will consider that the range is empty if we miss any cells, as every cell is equally important to us. The formula that we will create will count the empty cells in our range. If there are any, we will consider that the range is empty.

The formula will be as follows:

This is what our formula looks like in the VBA:

This code declares four variables: j and r as long (numbers), and rng and cell as a range. Then it sets the rng variable to look at our table (range A1:G10).

Then we get to an integral part of our code, which is For Next Loop. We define that for every cell in our range, we check if it is empty or not, and then we count the number of empty cells. Inside our loop, we make sure that we go to the next cell in our range when we finish checking the one before.

Finally, we display the message box that shows the number of empty cells in our range.

When we execute the code by pressing F5 on our keyboard, this is the message that we will have:

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.