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.

VBA to Check If Single Cell is Empty

Step 2:

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

VBA to Check If Single Cell is Empty

Step 3:

  • Write the following VBA code on the command module.
Sub Check_Empty()
MsgBox IsEmpty(Range("B7"))
End Sub

VBA to Check If Single Cell is Empty

We want to check Cell B7 here.

Step 4:

  • Now, press F5 to run the code.

VBA to Check If Single Cell is Empty

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

Check If Multiple Cells are Empty with Excel VBA

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 Delete Empty Cells in Excel (6 Methods)


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

Highlight Empty Cells with Excel VBA

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)


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

Excel VBA to Check If Active Cell is Empty

Step 2:

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

Excel VBA to Check If Active Cell is Empty

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: VBA to Count Blank Cells in Range in Excel (3 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

Check If a Range Contains any Empty Cell in Excel

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)


Similar Readings:


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.

Check Empty Cells from a Named Range

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

Check Empty Cells from a Named Range

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 Return Value if Cell is Blank (12 Ways)


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

VBA Code to Check If a Cell is Empty or Show the Value

Step 2:

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

VBA Code to Check If a Cell is Empty or Show the Value

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

VBA Code to Check If a Cell is Empty or Show the Value

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

Check Empty Cells and Show Status in the Immediate Box

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.

heck Empty Cells and Show Status in the Immediate Box

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

Check If All Cells are Empty or Not in a Range with Excel VBA

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

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo