These pages are no longer updated and are only available for archive purposes.
Click here to visit the pages with updated information.
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
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End FunctionExample: =CountUniqueValues(A1:A100) will count the unique values in the range A1:A100.
Document last updated 1999-08-14 12:44:24
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!