Example
Function countUnique(r As range) As Long
'Application.Volatile False ' optional
Set r = Intersect(r, r.Worksheet.UsedRange) ' optional if you pass entire rows or columns to the function
Dim c As New Collection, v
On Error Resume Next ' to ignore the Run-time error 457: "This key is already associated with an element of this collection".
For Each v In r.Value ' remove .Value for ranges with more than one Areas
c.Add 0, v & ""
Next
c.Remove "" ' optional to exclude blank values from the count
countUnique = c.Count
End Function
Collections
