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:
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:
To check if a certain cell is empty or not, we will insert a simple code:
If IsEmpty(Range("A1")) = FALSE Then
MsgBox "Value in our cell Is Not empty"
MsgBox "Value in our cell Is empty"
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:
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:
Dim j As Long
Dim r As Long
Dim rng As Range
Dim cell As Range
Set rng = Range("A1:G10")
For Each cell In rng
r = r + 1
If IsEmpty(cell) Then
j = j + 1
"There are total " & j & " Empty cell(s) out of " & r & "."
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: