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.)
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
Application.Volatile
For Each Cell In Rng
If Len(Cell.Formula) = 0 Then Fun = Fun + 1
Next Cell
CountEmpty = Fun
End FunctionCall the function from the worksheet with =CountEmpty(A1:B10).
What about using a formula like this:
=ROWS(A1:A5)-COUNTA(A1:A5)It would work for one column.
If you want it to work on multiple columns you can do this:
=(ROWS(1:5)*COLUMNS(A:B))-COUNTA(A1:B5)