Introduction to Cells

In Microsoft Excel, a cell is nothing but an intersection of a column and a row. This cell is referenced by its row number and column letter. A cell can hold a formula/data directly. It can be formatted with font styles and colors or background colors too.

In the image above, cell B4 is selected. It is the intersection of row number “4” and column letter “B.” Since the value of the cell does not start with an “=”, it means that the cell is holding some data directly. Here the data held is “110.”

Empty Cells in Excel

If the cell does not have any value, it is said to be empty. There’s a chance that a cell has the same font color and background color, but with some data. In that case, it may look empty but actually isn’t. So, to find this, we have to select the cell and check the formula bar.

Here the selected cell D9 looks blank but has a value “India.” It is not visible on the cell because the color of the font in that cell is white, which is equal to its background color.

ISBLANK Formula

Microsoft Excel offers a formula named ISBLANK which returns/provides a Boolean result stating whether the value of that cells is blank or not.

Syntax:

ISBLANK( <value> )

Where <value> can be a reference to a cell.

In the above images, the selected cells hold the formula as in the formula bar referring to the cells B7 (first image) and C8 (second image) respectively. The false and true values are returned depending on the values in the cells referred as parameters.

The selected cells hold the formula seen in the formula bar referring to the cells B7 (first image) and C8 (second image) respectively. The false and true values are returned depending on the values in the cells referred as parameters.

Range of Cells in Excel

In Excel a range is a set of continuous cells which can be a part of one or more columns or one or more rows or both. For example, in this image above Range(“A1:B12”) can be termed as a range.

Empty Cells in a Range

If there is a need to find the empty cells in a huge range of cells, it might be time consuming and tiring to select each cell and find this manually. 

VBA to Find Empty Cells

VBA offers an inbuilt function called “IsEmpty” to do this for us.

Syntax

<Var> = IsEmpty ( <expression> )

Where <var> is any Boolean variable that can hold the end result value  (return value) of the function and <expression> is any value that needs to be checked if empty.

For Example:

Select a Cell and Display if it is Empty

Sub empty_demo()

' select an empty cell
Cells(1, 5).Select

' display a msg to cross check if the selected cell is empty
MsgBox IsEmpty(Cells(1, 5).Value)

End Sub

The same with a non-empty cell:

Sub empty_demo()

' select a non-empty cell
Cells(1, 1).Select

' display a msg to cross check if the selected cell is empty
MsgBox IsEmpty(Cells(1, 1).Value)

End Sub

Another Simple Program to Check if a Cell is Empty

Sub demo2()

' check if the value of a particular cell is nothing or ""
' if there is a value, the value is displayed . If not, a statement is displayed
If Cells(3, 4).Value = "" Then
    MsgBox "The cell in 3rd row and 4th col is empty"
Else
    MsgBox Cells(3, 4).Value
End If
End Sub

This program looks if the value of a specific cell is empty using just the “”. The same can also be done on several cells using a loop/range.

VBA – Find Empty Cells in a Range

It is possible to find the cells that are empty in a range of cells. Once a range is defined, we can loop through each cell in the range. As we loop, we can check if the cell is empty.

Program to Count the Number of Empty Cells in a Range of Cells

In this program:

1. We declare a count variable and initialize it to “0.” This will help us count the number of empty cells in a range.
2. We define a range in an Excel worksheet.
3. Then we loop through the cells in it using a “for each” loop. Inside the loop, we check if the cell is empty/blank using the inbuilt VBA function “ISEMPTY()”.
4. If so, the value of the “cnt” variable is incremented by “1.” Once we come out of the loop, we display the value of “cnt” as the number of empty cells in the declared range.

Sub empty_demo()

' declare a range
Dim myrange

'declare a variable to store count
Dim cnt

' define the range  and initialize count
myrange = Range("A1:A20")
cnt = 0

'loop through each cell of the range
For Each cell In myrange
    ' if the cell is empty , we increment the value of cnt variable by 1
    If IsEmpty(cell) Then
        cnt = cnt + 1
    End If
    
Next cell

' display the number of empty cells
MsgBox "There are " & cnt & " empty cells in the mentioned range. "

End Sub

Color the Empty Cells in a Range of Cells Without Defining Any Range

In this program, instead of using a range, we use a nested “for loop” to iterate through every row in every column. The outer loop iterates through the columns while the inner loop iterates through the rows. The upper and lower bounds of the desired range/table are already provided in the “for loop” definition. Inside the inner loop, as in the previous example, we check if the cell is empty and add a number to the “cnt” variable’s value if so. Here, in addition, we also color those empty cells that are identified within the inner loop’s condition.

Sub empty_demo_1()

' declare variables to indicate, row, col and counter
Dim r, c, cnt

' intialize the variables
r = 1
c = 1
cnt = 0

' loop through each col in the worksheet till we reach col no "2"
For c = 1 To 2
' loop through each row in the worksheet till we reach row no "20"
For r = 1 To 20
' if the cell ( intersection of the row and col) is empty , we increment the value of cnt variable by 1
If IsEmpty(Cells(r, c).Value) = True Then
cnt = cnt + 1
' Color  the cells in yellow just to identify
Cells(r, c).Interior.Color = 65535
End If
Next r

Next c
' display the number of empty cells
MsgBox "There are " & cnt & " empty cells in the mentioned range. "

End Sub

Using IsEmpty() for Variable Values

This program checks if a variable is empty and displays the result.

Sub demo3()

' declare a variable and do not initialize
Dim var1

' check if variable has value
If IsEmpty(var1) = True Then
' statement if empty
Debug.Print "Var1 is Empty"
Else
' statement if not empty
Debug.Print "Var1 has a value " & var1
End If

End Sub

Output would be:

“Var1 is Empty”

Sub demo3()

' declare a variable and initialize it
Dim var1
var1 = "This is a good day"

' check if variable has value
If IsEmpty(var1) = True Then
' statement if empty
Debug.Print "Var1 is Empty"
Else
' statement if not empty
Debug.Print "Var1 has a value. " & var1
End If

Output would be:

“Var1 has a value. This is a good day”

Conclusion

Apart from cells and variables, the IsEmpty() function offered by VBA can be used for arrays and many such objects. However, these are not elaborated here as the focus of this article is on checking if a cell is empty. The “” ( empty double quotes) can also be used to check if the cell(s) are empty. This is just a replacement for the IsEmpty() function and straightforward too. At times, we get stuck into a situation where nothing works out to validate if a cell is empty. In that case we can also try the len() function. The length of the cell value is “0” if it is actually empty. 

Check out our other articles on the specific functions to know much about their usage.