In this article, we will create a custom function to count the unique values in the defined range.
Raw data for this example consists of random numbers in the rows. In this example, we want to count the number of unique numbers in each row.
We have created “CountUniqueValues” custom function to find the count of unique numbers in the row. This function takes range as input and returns the unique count of numbers.
We have created custom function “CountUniqueValues” to get the count of unique numbers. In this custom function, we have created object of the collection object. This collection object is used to create the list of unique numbers. Once we have the list of unique numbers, we get the count of items in the collection.
Application.Volatile is used because it will recalculate every time a cell value is changed in the workbook.
On Error Resume Next is used to handle errors.
Please follow below for the code
Option Explicit Function CountUniqueValues(InputRange As Range) As Integer Dim CellValue As Variant, UniqueValues As New Collection Application.Volatile 'For error Handling On Error Resume Next 'Looping through all the cell in the defined range For Each CellValue In InputRange UniqueValues.Add CellValue, CStr(CellValue) ' add the unique item Next 'Returning the count of number of unique values CountUniqueValues = UniqueValues.Count 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 email@example.com
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.