Excel VBA to Count Blank Cells in Range (3 Methods)

When we have a large amount of data in our Excel worksheet, it is easier if we have the knowledge of the total counts of certain ranges of the sheet. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to count blank cells in a range in Excel with the VBA macro.

Excel VBA to Count Blank Cells in Range: 3 Methods

Look at the dataset shown in the image below. This dataset consists of many blank cells.

We will learn how to count the blank cells of this dataset for specific ranges with the COUNTIF Function, the COUNTBLANK Function, and the range selected by users in Excel with VBA macro.

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

To count blank cells in a range with the COUNTIF function, you have to define the range inside the function. That range can be a column or a row.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, copy the following code and paste it into the code window.

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

Your code is now ready to run.

This piece of code will count the blank cells in the B Column, range B4:B9.

• Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the successful code execution, look at the image below to see the result.

There will be a pop-up message box showing you the total count of the blank cells situated in the range specified. We have 1 blank cell in range B4:B9, so we got 1 as the total count.

You can also count the blank cells in a row with the COUNTIF function. The code is pretty similar to the above one. But this time, all you have to do is define the range of a row instead of a column.

• To calculate the blank cells in row 5 for our dataset, the code is:

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

This piece of code will count the blank cells for the row of range B5:F5.

• Now, Run the macro.

There will be a pop-up message box showing you the total count of the blank cells situated in the range specified. We have 2 blank cells in range B5:F5, so we got 2 as the total count.

2. Implement Macro to Calculate Empty Cells with Excel’s COUNTBLANK Function

You can also utilize Excel’s COUNTBLANK function to count blank cells in a range. You just have to define the range inside the function just like we discussed in the previous section. That range can be a column or a row.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, in the code window, copy the following code and paste it.

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

Your code is now ready to run.

• Now, Run this code and look at the image below to see the result.

There will be a pop-up message box showing you the total count of the blank cells situated in the range specified. We have 1 blank cell in range B4:B9, so we got 1 as the total count.

You can also count the blank cells in a row with the COUNTBLANK function. The code is pretty similar to the above one. But this time, all you have to do is define the range of a row instead of a column.

• To calculate the blank cells in row 5 for our dataset, the code is:

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

This piece of code will count the blank cells for the row of range B5:F5.

• Now, Run the macro.

Finally, there will be a pop-up message box showing you the total count of the blank cells situated in the range specified. We have 2 blank cells in range B5:F5, so we got 2 as the total count.

3. Apply VBA Macro to Compute Blank Cells in a Range in Excel

Now, we will see how to count blank cells in a range that will be specified by users.

Steps:

• As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, in the code window, copy the following code and paste it.

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

Your code is now ready to run.

• Now, Run the macro.
• A pop-up input box will appear. Select the range that you want to find and how many blank cells are available in there by dragging (in our case, the range is B4:F9).
• Then, press OK.

Now, notice the following image.

As a result, Excel’s message box will show you how many blank cells are there in your dataset. For our dataset, there are 8 blank cells in the whole range B4:F9.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

To conclude, this article showed you 3 different methods for counting blank cells in a range in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

<< Go Back to Count Cells | Formula List | Learn Excel