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


Download Workbook

You can download the free practice Excel workbook from here.


3 Methods with VBA to Count Blank Cells in Range in Excel

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

Dataset of excel vba count blank cells in range

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.

excel vba count blank cells in range of column with COUNTIF

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.

result of excel vba count blank cells in range or column with COUNTIF

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.

excel vba count blank cells in range of row with countif

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

Read More: How to Remove Blank Cells from a Range in Excel (9 Methods)


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.

excel vba count blank cells in range of column with countblank

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

result of excel vba count blank cells in range of column with countblank

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.

excel vba count blank cells in range of row with countblank

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

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


Similar Readings:


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.

VBA to count blank cells in range in Excel

  • Now, Run the macro.
  • A pop-up input box will appear. Select the range that you want to find 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.

result of VBA to count blank cells in range in Excel

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.

Read More: How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)


Conclusion

To conclude, this article showed you 3 different methods on how to count 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.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo