PDA

View Full Version : Function to count values but not formulae



omp001
08-27-2011, 09:16 AM
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
08-27-2011, 09:38 AM
Perhaps like this

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

omp001
08-27-2011, 09:48 AM
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
08-27-2011, 10:04 AM
Try this

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

Then use it like

=countvals(A1:J20)

omp001
08-27-2011, 10:29 AM
Hi VoG.
Working fine. Thanks so much.
Nice wknd.

Oswald