PDA

View Full Version : Solved: Counting Cells Where Formula Does Not Return ""



mbake16
05-24-2009, 11:18 AM
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
05-24-2009, 11:59 AM
CellCount = Application.WorksheetFunction.CountIf(Sheet2.Range("a1:k1"), "> """)

xld
05-24-2009, 12:26 PM
CellCount = Application.Evaluate("=SUMPRODUCT(--(LEN(A1:K1)>0))")

mbake16
05-24-2009, 01:18 PM
Thank you both for your help. Both formulas work perfectly!!

Thank you very much!


Matt