Excel VBA: Check If Multiple Cells Are Empty (9 Examples)

A Microsoft Excel file usually contains data in each cell. Sometimes some cells remain empty. To detect empty cells in Excel is tiresome work. We can use Excel VBA to check if multiple cells are empty and reduce the time consumption.

Excel VBA: Check If Multiple Cells Are Empty: 9 VBA Codes

In this article, we will check multiple empty cells using Excel VBA code. We will consider the following dataset for this.

1. VBA to Check If a Single Cell Is Empty

We will check if a single cell is empty or not in this section.

Step 1:

Go to the Developer tab.
Click on the Record Macro option.
Set a name for the macro and click OK.

Step 2:

Click the Macros option from the ribbon.
Select the Macro from the list and Step Into it.

Step 3:

Write the following VBA code on the command module.

Sub Check_Empty()
MsgBox IsEmpty(Range("B7"))
End Sub

We want to check Cell B7 here.

Step 4:

Now, press F5 to run the code.

The result is True as Cell B7 is empty.

Step 5:

We want to check another Cell B5. So, replace B7 with B5 in the VBA code.

Sub Check_Empty()
MsgBox IsEmpty(Range("B5"))
End Sub

Step 6:

Again, press F5 to run the code.

The result is False, as Cell B5 contains data.

2. Check If Multiple Cells Are Empty with Excel VBA

We will check the range of cells and find out how many cells are empty of that range.

Step 1:

Press Alt+F11 to enter the command module.
Copy and paste the following VBA code on the module.

Sub Check_Empty_1()
Dim n, m As Long
Dim range_1 As range
Dim cell_1 As range
Set range_1 = range("B5:D9")
For Each cell_1 In range_1
    m = m + 1
    If IsEmpty(cell_1) Then
        n = n + 1
    End If
Next cell_1
MsgBox _
"Out of " & m & " no. of empty cell(s) " & n & "."
End Sub

We will check cells of Range B5:D9. We will get how many cells are empty out of those 15 cells in the result.

Step 2:

Run the code by pressing the F5 button.

5 cells are empty out of 15 cells.

3. Highlight Empty Cells with Excel VBA

We will highlight the empty cells from a given range in this section.

Step 1:

Enter the VBA command module by pressing Alt+F11.
Copy and paste the code below in the module.

Sub Check_Empty_2()
Dim n, m As Long
Dim range_1 As range
Dim cell_1 As range
Set range_1 = range("B5:D9")
For Each cell_1 In range_1
    m = m + 1
    If IsEmpty(cell_1) Then
    cell_1.Interior.Color = RGB(255, 87, 87)
        n = n + 1
    End If
Next cell_1
End Sub

Step 2:

Hit F5 and run the code.

The empty cell of range B5:D9 is highlighted in red color.

4. Excel VBA to Check If Active Cell Is Empty

This example is to check active or selected cell is empty or not. We can check a single cell by this method.

Step 1:

Hit Alt+F11 and enter the command module.
Copy the following VBA code and paste it into the module.

Sub Check_Empty_3()
 If IsEmpty(ActiveCell) Then
        MsgBox "Selected cell is empty"
    Else
        MsgBox "Selected cell is not empty"
    End If
End Sub

Step 2:

Now, select Cell C5 and run the code by pressing F5.

The result is showing empty as Cell C5 is empty.

Step 3:

We want to check another cell. So, select Cell B8 now.
Again, press F5 to run the code.

The result is not empty as Cell B8 contains data.

5. Check If a Range Contains Any Empty Cell

In this section, we will check if a range contains any empty cells or not.

Step 1:

Hit Alt+F11 and enter the command module.
Copy the following VBA code on the module.

Sub Check_Empty_4()
    Dim range_1 As range
    Set range_1 = range("B5:D9")
    If WorksheetFunction.CountA(range_1) < range_1.Count Then
        MsgBox range_1.Address & " range has minimun 1 empty cell"
    Else
        MsgBox range_1.Address & " doesn't have any empty cell"
    End If
End Sub

Step 2:

Run the code by pressing the F5 button.

Our given range has empty cells and that is shown in the result.

6. Check Empty Cells from a Named Range

Now, we will check empty cells from a Named Range.

Step 1:

Go to the Formula tab.
Choose the Define Name option from the Defined Names group.
Set the name of the Range and then press OK.

Step 2:

Now, enter the command module by pressing Alt+F11.
Copy and paste the following VBA code.

Sub Check_Empty_5()
 For Each cell In range("Name")
    If IsEmpty(cell) Then
       MsgBox ("Empty Cell")
    End If
    Next
End Sub

Step 3:

Now, press F5 to run the code.

Now, we get the result. As we can see there are two empty cells in the ranges, we must get 2 empty cells in the result. Then, press OK on the Result box and we will get another dialog box showing empty cells.

7. Alternative VBA Code to Check If a Cell Is Empty or Show the Value

In this section, we will select a cell by the input box method and check if that cell is empty or not. If the cell is not empty, then show the value of that cell.

Step 1:

Hit Alt+F11 to enter the command module.
Put the following VBA code on the module.

Sub Check_Empty_6()
Dim range_1 As range
    Set range_1 = Application.InputBox(prompt:="Sample", Type:=8)
 If range_1.Value = "" Then
    MsgBox "Selected cell is empty"
Else
    MsgBox "Value of the cell is: " & range_1.Value
End If
End Sub

Step 2:

Now, run the code by pressing F5.
An input box will appear to input the cell. Select Cell C5 and press OK.

Look at the worksheet. As Cell C5 is not empty, it shows the value of that cell.

Step 3:

Again, run the code and select Cell C7 in the input box.
Then press OK.

The result is showing empty as Cell C7 is empty.

8. Check Empty Cells and Show Status in the Immediate Box

We can check our cells from the Immediate Window of the VBA.

Step 1:

Select range B5:D9 first.
Hit Alt+F11 to enter the command module.
Copy and paste the following VBA code on the module.

Sub Check_Empty_7()
Dim range_1 As range
Set range_1 = Selection
For Each cell In range_1
    If IsEmpty(cell.Value) = True Then
    Debug.Print ("Empty")
    Else
        Debug.Print ("Not Empty")
    End If
Next cell
End Sub

Step 2:

Press F5 to run the code.
The result will be shown on the Immediate Window. Go to the View tab.
Select Immediate Window from the list or press Ctrl+G.

Look at the Immediate window now.

The status of each cell is shown here.

9. Check If All Cells Are Empty or Not in a Range with Excel VBA

We want to check if all the cells are empty or not in a range.

Step 1:

Hit Alt+F11 and enter the command module.
Copy and paste the following VBA code.

Sub Check_Empty_8()
Dim range_1 As range
Set range_1 = range("B5:D9")
    For Each cell In range_1
        If cell.Value = "" Then
            GoTo NextStep
        Else
            MsgBox "All cells are not empty."
            GoTo EndSub
        End If
    Next
NextStep:

EndSub:
End Sub

Step 2:

Run the code by pressing F5.

Look at the result. We have both blank and cells with data in our dataset that are reflected in the

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Check If Multiple Cells Empty.xlsm

Conclusion

In this article, we showed examples of Excel VBA to check If multiple cells are empty. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.