ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

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
    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      Printerfriendly version

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!


Erlandsen Data Consulting   
Excel & VBA Tips   Copyright ©1999-2018    Ole P. Erlandsen   All rights reserved
E-mail Contact Address