Unique value - macro

[gbell3587]

Hi

I need to count the number of unique values on my worksheet. I have a formula that works fine when i enter the start and end point

=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))

I need to find them in an entire column a:a and this doesnt work. Also, i would ideally like to have this as a macro using application.worksheetfunction syntax but dont know how to do this.

Can anyone help?

Thanks
Graham

 

[shg]

One way:

Function CountUnique(r As Range) As Long
    Dim sAdr As String
    Dim sFrm As String
 
    sAdr = Intersect(r.Worksheet.UsedRange, r.Areas(1)).Address
    sFrm = "sumproduct((" & sAdr & " <> """")/countif(" & sAdr & ", " & sAdr & " & """"))"
    CountUnique = r.Worksheet.Evaluate(sFrm)
End Function

E.g., =CountUnique(A:B)

 

[gehusi]

As a non VBA alternative,

{=sum(1/countif(A1:A10,A1:10))}

You could replace A1:A10 with a dynamic named range.