Empty Cells VBA – Check, Count, and Highlight

Today we would learn about how to check if a cell is empty using VBA in Excel. We would also learn to count the total number of empty cells in a range of cells and highlight empty cells using VBA.

So let us begin.

How to Check If Cell is Empty?

We can refer to a cell using the Range property. The Isempty function returns a true/false depending on whether the cell is empty or not. For example, let us say we want to check if cell A2 of the active sheet is empty or not. then we use this Sub Procedure.

Sub is_A2_empty()
MsgBox "Is cell A2 empty? " & IsEmpty(Range("A2"))
End Sub

The cell A2 is not empty which means that we would get a False as result.

Note that this Sub Procedure works on the Cell A2 of the Active Sheet only.

Checking if a Cell is Empty in a Specific Worksheet

Let us suppose we have three worksheets in our workbook and we need to check whether the Cell A1 of Sheet2 is an empty cell.

In this situation, we use the Worksheets Collection to access the Sheet2 of the workbook.

Sub is_A1_Sheet2_Empty()
'check the cell A1 of Sheet2
MsgBox "Is cell A1 in Sheet2 empty? " & IsEmpty(Worksheets("Sheet2").Range("A1"))
End Sub

Counting the Total Number of Empty Cells in a Range using VBA

In this example, we would learn to count and print the total number of empty cells in a range. We would use a countEmpty variable to count the total number of empty cells. The code will loop through each cell of the range and check if the cell is empty and then increment the countEmpty variable by 1.

Sub count_empty_cells()
'counting the total number of empty cells in range A1:D10
Dim countEmpty As Integer
Dim cell As Range
'loop through each cell in range A1:D10
For Each cell In Range("A1:D10")
    'check if the cell is empty
    If IsEmpty(cell) Then
        'increment the count of empty cell by 1 if condition is true
    countEmpty = countEmpty + 1
    End If
Next cell
'print the total number of empty cells in the range
MsgBox "number of empty cells in range A1:D10 are: " & countEmpty
End Sub

When you run this Sub Procedure, you can see that we have 34 empty cells out of 40 total cells in the range A1:D10.

Highlighted is the range in which we loop to get the number of empty cells. Here Cell A2, A6, B9, C4, C7, and D8 contain either x or o which makes them nonempty. Therefore the total number of empty cells in the range is 34.

Highlight the Empty Cells in a Range

In this example, we would make a named range and then highlight the empty cells using VBA. Let us say we have the following set of records.

• Select the range B2:B20.
• Go to the name box and type “Phone” and hit enter key to name this range.

Now we have a named range Phone. We would count the total number of empty cells to get the number of people whose phone number is not stored in these records and then highlight their corresponding IDs to the left of the Phone Number.

Sub count_highlight_empty_cells()
Dim count As Integer
Dim cell As Range
'loop through each cell in Phone Named Range
For Each cell In Range("Phone")
'check if the cell is empty
    If IsEmpty(cell) Then
        'increase the number of empty cell by 1
        count = count + 1
        'change the color of the cell to the left (containing id) of phone 
        ' -1 is to move one column before
        cell.Offset(0, -1).Interior.Color = RGB(99, 255, 204)
    End If
Next cell
'print the number of empty cells
MsgBox "number of phone numbers not in record are:" & count
End Sub

Here we have used the offset property of the Range object named cell to refer to the cell (containing the corresponding ID) at one column to the left of the current cell (empty cell in Phone Range ) in the loop.

The RGB function is used for hexadecimal color codes.

When you run this Sub Procedure, the ID of persons with no phone number is highlighted along with the number of blank phone number entries.

This brings us to an end.

Thank you for reading.