Excel formula into VBA

Question

How to implement this formula =SUMPRODUCT(--(LEN(A1:A100)>0)) in VBA. I got Type Mismatch error.

This is what I did in vba, but not working:

WorksheetFunction.SumProduct(--(Len(Worksheets("Sheet1").Range("A1:A100")) > 0))

Answer 1

Try to use:

Evaluate("=SUMPRODUCT(--(LEN(Sheet1!A1:A100)>0))")

If you want to calculate only non-empty cells, you can use:

WorksheetFunction.CountA(Worksheets("Sheet1").Range("A1:A100"))