Count Unique Values

How many times have you had a long list of values and needed to know how many were unique values? This function will go through a range and tell you just that, as shown in Figure 4.7.

NumUniqueValues(Rng) The argument is

Rng—The range to search unique values. Function example:

Function NumUniqueValues(Rng As Range) As Long Dim myCell As Range, UniqueVals As New Collection

Application.Volatile 'forces the function to recalculate when the range changes On Error Resume Next

'the following places each value from the range into a collection because 'a collection can contain only unique values, there will be no duplicates the 'error statements force the program to continue when the error messages 'appear for duplicate items in the collection For Each myCell In Rng

UniqueVals.Add myCell.Value, CStr(myCell.Value) Next myCell On Error GoTo 0

'returns the number of items in the collection NumUniqueValues = UniqueVals.Count End Function

Figure 4.7

Count the number of unique values in a range.

0 0

Post a comment