Count unique values using VBA in Microsoft Excel

My Account

Check out

Shopping Cart

JOIN OUR NEWSLETTER
and receive for joining:
	Free eBook Learn More!
	Free Excel ADD-IN
	Free Weekly Excel Tip








F1 eBook (Spanish)

» Count unique values using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting* offering Microsoft Excel Application development, template customization, support and training solutions*

CATEGORY: Custom Functions , Printing in VBA	VERSIONS: All Microsoft Excel Versions

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.