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.

Download Practice Workbook

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

9 VBA Codes to Check If Multiple Cells Are Empty in Excel

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.

Read More: How to Make Empty Cells Blank in Excel (3 Methods)

2. Check If Multiple Cells Are Empty with Excel VBA

We will check a 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 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.

Read More: How to Find Blank Cells in Excel (8 Easy Ways)

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 by red color.

Read More: Highlight Blank Cells in Excel (4 Fruitful Ways)

Similar Readings

• Fill Blank Cells with Text in Excel (3 Effective Ways)
• How to Fill Blank Cells with Formula in Excel (2 Easy Methods)
• Fill Blank Cells with Dash in Excel (3 Easy Ways)
• How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)
• Fill Blank Cells with Color in Excel (5 Methods)

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 on 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.

Related Content: How to Find Blank Cells Using VBA in Excel (6 Methods)

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 has shown in the result.

Read More: Excel VBA: Find the Next Empty Cell in Range (4 Examples)

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.

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)

Similar Readings

• How to Return Value if Cell is Blank (12 Ways)
• If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)
• How to Skip Blank Rows Using Formula in Excel (8 Methods)
• Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
• How to Delete Blank Cells in Excel and Shift Data Up

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.

Read More: If Cell is Blank Then Show 0 in Excel (4 Ways)

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.

Related Content: Find If Cell is Blank in Excel (7 Methods)

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

Read More: How to Ignore Blank Cells in Range in Excel (8 Ways)

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.

Related Articles

• How to Apply Conditional Formatting in Excel If Another Cell Is Blank
• Remove Blanks from List Using Formula in Excel (4 Methods)
• How to Set Cell to Blank in Formula in Excel (6 Ways)
• Remove Blank Lines in Excel (8 Easy Ways)
• Return Non Blank Cells from a Range in Excel (3 Suitable Ways)
• Excel If Two Cells Are Blank Then Return Value
• VBA to Count Blank Cells in Range in Excel (3 Methods)