Using Excel VBA to Count Blank Cells in a Range - 3 Methods

This dataset contains blank cells.

Use the COUNTIF Function and the COUNTBLANK Function in a VBA macro.

Method 1 – Embed a VBA to Count Blank Cells in a Range with the COUNTIF Function

Steps:

Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.

In the code window, click Insert -> Module.

Enter the following code in the code window.

Sub CountBlankWithCountIfColumn()
    MsgBox WorksheetFunction.CountIf(Range("B4:B9"), "")
End Sub

The code will count the blank cells in B4:B9.

Press F5 or click Run -> Run Sub/UserForm. You can also click the Run icon.

This is the output.

A message box displays the total count of blank cells in the range: 1.

You can also count blank cells in a row with the COUNTIF function.

To calculate the blank cells in row 5, enter the code:

Sub CountBlankWithCountIfRow()
    MsgBox WorksheetFunction.CountIf(Range("B5:F5"), "")
End Sub

The code will count blank cells in B5:F5.

Run the macro.

A message box displays the total count of blank cells in the range: 2.

Method 2 – Applying a Macro to Calculate Empty Cells with Excel’s COUNTBLANK Function

Steps:

Open the Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub CountBlankWithCountBlankColumn()
  MsgBox WorksheetFunction.CountBlank(Range("B4:B9"))
End Sub

Run the code.

This is the output.

A message box displays the total count of blank cells in the range: 1.

You can also use the COUNTBLANK function. The code is similar, but you need to define the row range, instead of the column range.

To calculate the blank cells in row 5, enter the code:

Sub CountBlankWithCountBlankRow()
    MsgBox WorksheetFunction.CountBlank(Range("B5:F5"))
End Sub

The code will count blank cells in B5:F5.

Run the macro.

A message box displays the total count of blank cells in the range: 2.

Method 3 – Applying a VBA Macro to Compute Blank Cells in a Range in Excel

Steps:

Open the Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub CountBlankCells()
Dim iRange As Range
Dim iInput As Range
Dim iCount As Long
On Error Resume Next
xTitleId = "Microsoft Excel"
Set iInput = Application.Selection
Set iInput = Application.InputBox("Select Range", xTitleId, iInput.Address, Type:=8)
For Each iRange In iInput
    If IsEmpty(iRange.Value) Then
        iCount = iCount + 1
    End If
Next
MsgBox "There are " & iCount & " blank cells in the range"
End Sub