Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Count Blank Cells Across an Entire Table


Hi everyone, 

How do I count all of the blank cells across an entire table?

I need to count cells that are completely empty and also cells that just don't output anything but can have a formula inside.

I am using COUNTBLANK() for a single column and that works to count empty cells and cells whose formulas don't output a value, but how do I count just those cells that have literally nothing inside of them?

Thanks in advance!




You would need a User Defined Function (UDF) for that. Paste the code below into a standard code module (by default "Module1" before you rename it), and save the workbook as macro enabled (xlsm format).

Function CountEmpty(Rng As Range) As Long
    Dim Fun As Long
    Dim Cell As Range
    For Each Cell In Rng
        If Len(Cell.Formula) = 0 Then Fun = Fun + 1
    Next Cell
    CountEmpty = Fun
End Function

Call the function from the worksheet with =CountEmpty(A1:B10).


What about using a formula like this:


It would work for one column.

If you want it to work on multiple columns you can do this:


Answer the Question

You must create an account to use the forum. Create an Account or Login