 # count non-blank cells in range w/VB

• I seem to be 'search challenged' today - I'm sure this is easy. How does one count the number of non-blank cells in a range using VBA?

• Re: count non-blank cells in range w/VB

Here's one way

Code
``````Sub count()
MsgBox WorksheetFunction.CountA(Range("A1:D45"))
End Sub``````
• Re: count non-blank cells in range w/VB

Thank you, that works! : D Thinking ahead to possible future use, is there a way to keep VBA from counting a cell that is blank but has a formula producing that empty value?

• Re: count non-blank cells in range w/VB

Technically, if a cell has a formula in it, it's not blank. You may need to cycle (loop) through each cell in the range and count it only if its LEN = 0.

• Re: count non-blank cells in range w/VB

Ah, the notorious loop, what I was trying to get away from. This did the trick:

Code
``WorksheetFunction.CountA(Range("A1:D45")) - WorksheetFunction.CountBlank(Range("A1:D45"))``

Of course, this only works if EVERY cell in the range has a formula in it. :exclamat:

• Re: count non-blank cells in range w/VB

Hmmm .. you gfot me playing a bit. The following work as Excel formulas, now if they can be converted to VBA.

For non-blank cells.
=SUMPRODUCT((LEN(A1:E15)>0)*1)

For blank cells.
=SUMPRODUCT((LEN(A1:E15)=0)*1)

• Re: count non-blank cells in range w/VB

If you are going to write the answer to a cell, you could use something like:

Code
``````Sub count()
Range("G1") = "=SumProduct((Len(A1:E15) = 0) * 1)"
End Sub``````
• Re: count non-blank cells in range w/VB

Alas, it's going to become a variable that will be accessed later in the macro. I prefer not to have the data ever write to a cell. Pity!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!