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.
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.
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
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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.