Function to count values but not formulae

[omp001]

I need a function that would count how many cells house any value but not consider cells with formula, in the used range.

thanks in advance

[VoG]

Perhaps like this

[VBA]Sub CountVals()
Dim c As Long
c = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Count
MsgBox c
End Sub
[/VBA]

[omp001]

Hi VoG. Thanks for reply.

Is it possible to get the same result using a Function, I mean, using a UDF?

thanks again

Oswald

[VoG]

Try this

[VBA]Function CountVals(r As Range) As Long
Dim c As Range
For Each c In r
If c.Value <> "" And Not c.HasFormula Then CountVals = CountVals + 1
Next c
End Function[/VBA]

Then use it like

=countvals(A1:J20)

[omp001]

Hi VoG.
Working fine. Thanks so much.
Nice wknd.

Oswald