Counting Cells Where Formula Does Not Return ""

[mbake16]

Hello:

I'm trying to count range cells where the formula in the cell does not return "". When using the following code, it counts all cells containing a formula regardless of the value returned.


Sub CountNonBlankCells()
Dim CellCount As Long
CellCount = Application.WorksheetFunction.CountIf(Sheet2.Range("a1:k1"), "<>""")
MsgBox CellCount, vbOKOnly
End Sub

If anyone knows how to count only cells where the formula returns something other than "" your help would be greatly appreciated!

An example of what I'm trying to do is attached for reference.

Thanks,


Matt

[mikerickson]

CellCount = Application.WorksheetFunction.CountIf(Sheet2.Range("a1:k1"), "> """)

[Bob Phillips]

CellCount = Application.Evaluate("=SUMPRODUCT(--(LEN(A1:K1)>0))")

[mbake16]

Thank you both for your help. Both formulas work perfectly!!

Thank you very much!


Matt