Sanjida Ahmed
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.
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 SubYour 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 SubThis 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.
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 SubYour 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 SubThis 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.
Read More: How to Make Empty Cells Blank in Excel (3 Methods)
Similar Readings:
- How to Find Blank Cells Using VBA in Excel (6 Methods)
- Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)
- Excel VBA: Find the Next Empty Cell in Range (4 Examples)
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- Remove Blanks from List Using Formula in Excel (4 Methods)
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 SubYour 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 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.
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.
