Count unique values

The custom function below counts the unique values only from a given range. This function uses the fact that a collection can only hold items with unique identifications keys. If you try to add an item with a key that already exists in the collection, an error will occur.

Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
    Application.Volatile
    On Error Resume Next ' ignore any errors
    For Each cl In InputRange
        UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
    Next cl
    On Error GoTo 0
    CountUniqueValues = UniqueValues.Count
End Function

Example: =CountUniqueValues(A1:A100) will count the unique values in the range A1:A100.

Document last updated 1999-08-14 12:44:24

User comments:

Ole P. from Norway wrote (2006-03-06 09:22:00 CET):

Re: Excel 2002 error
You are probably missing some references to required libraries in VBA.
Open the VBE editor and activate a default workbook.
Select Tools, References... and note the libraries that are checked.
Activate the problem workbook and select Tools, References...
Now you will probably find that one or more default libraries are not checked. Remove any checkmarks from "missing" libraries.

It is normal that the file size increases when using the Excel 97 - 2002 file option. I usually don't use this file format.

Teresa from USA wrote (2006-03-05 18:14:24 CET):

Excel 2002 error
I have been using the "CountUniqueValues" code for years and just love it ...

I now have Excel 2002 and am encountering "run time errors" with sheets containing this code.

I've tried saving the sheets as "Excel 97-2002" type, but the file size tripples.

Any help would be SUPER!