Finding unique values using VBA in Microsoft Excel

In this article, we will create custom function to find unique values from the defined range.

Raw data for this example consists of duplicate entries of country name in the range A8:A21.

ArrowRawData

Logic explanation

We have created a custom function “UniqueItem” to extract the unique values from the range. This function takes two parameters as input. First parameter is the range which consists of the duplicate data and second parameter defines the index number of a unique value in the unique collection.

This function returns the unique value from the range based on the index number. Second parameter is an optional parameter, if second parameter is not specified then function returns the number of unique value in the defined range.

If the value defined for the second parameter is greater than number of unique values in the range then last unique value is return by the function.

ArrowOutput

Code explanation

On Error Resume Next

For Each CellValue In InputRange

CUnique.Add CellValue.Value, CStr(CellValue.Value)

On Error GoTo 0

Above code is used to create collection object which will contain a unique value from the defined range. “On Error” statements are used to handle errors, as when code tries to put duplicate values in the collection, collection will generate an error.

Please follow below for the code


Option Explicit

Function UniqueItem(InputRange As Range, Optional ItemNo As Integer = 0) As Variant

Dim CellValue As Range
Dim CUnique As New Collection
Dim UCount As Integer

On Error Resume Next

'Adding unique items to collection from defined range
For Each CellValue In InputRange
    CUnique.Add CellValue.Value, CStr(CellValue.Value)
Next

On Error GoTo 0

'Getting count of unique item in the collection
UCount = CUnique.Count

If ItemNo = 0 Then
    'Returning count of unique item
    UniqueItem = UCount
ElseIf ItemNo <= UCount Then
    'Returning unique value based on item number
    UniqueItem = CUnique(ItemNo)
Else
    'Returning last unique value
    UniqueItem = CUnique(UCount)
End If

End Function

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com