How To Count Unique Value And Distinct Value In Excel Using VBA

In the previous article, I told you how to use the Excel formula to count unique values and distinct values in Excel. In this article, I will tell you how to define customized VBA functions to do the same thing using VBA.

1. Insert VBA Module In Excel Worksheet.

1. This example will use the data tables in the article How To Use Excel Formula To Count Unique Value In Excel And Count Distinct Value In Excel.
2. Below is the cell table data.
   
3. Open the Excel worksheet, and click the Developer tab —> Visual Basic item in the Code group.
4. Then it will open the Microsoft Visual Basic for Applications window.
5. Click the Insert menu item in the window’s top area.
6. Then click the Module menu item in the drop-down menu list to insert a module in the VBA window.
7. You can read the article How To Enable Developer Tab And Write VBA Source Code In Excel if you can not find the Developer tab in Excel.

2. How To Count Unique Value In Excel Using VBA.

1. Now input the below VBA source code in the above VBA module file to define the function Count_Unique_Value.

   Function Count_Unique_Value(cellRange As Range) As Integer
   
       ' Define a variable to save a cell value.
       Dim cellValue As Variant
       
       ' Define a collection variable to save unique cell values.
       Dim uniqueValues As New Collection
       
       ' Define the variable to record whether the cell value exist in the uniqueValues or not.
       Dim cellValueExist As Boolean
       cellValueExist = False
       
       ' Define this variable to save the existing item index in the uniqueValues collection object.
       Dim existItemIndex As Integer
       existItemIndex = 0
       
       
       ' The below code will make this user defined function recalculate when any cell of the worksheet caculate.
       ' If do not call the below code Application.Volatile, then this user defined function will only recaculate when the input parameter is changed.
       Application.Volatile
       
       ' Ignore errors and execute to the next line of code.
       On Error Resume Next
       
       ' Loop in the cell range.
       For Each cellValue In cellRange
       
          ' If the currnet cell value is a text value.
          If ActiveSheet.IsText(cellValue) Then
       
              ' Initialize the related variables.
              cellValueExist = False
              existItemIndex = 0
              
              ' If the cell value is not empty.
              If IsEmpty(cellValue) = False Then
              
                 ' Loop the items in the uniqueValues collection object.
                 For Each Item In uniqueValues
                  
                      ' Compare the cell value (lower case) to the item (lower case) in the uniqueValues collection object.
                      If LCase(Item) = LCase(cellValue) Then
                      
                         ' If the cell value exist in the uniqueValues collection object.
                         cellValueExist = True
                       
                      End If
                      
                      existItemIndex = existItemIndex + 1
                      
                      ' Exit the for loop when the cell value exist in the uniqueValues collection object.
                      If cellValueExist Then
                      
                         Exit For
                      
                      End If
                      
                      
                 Next
                      
                 ' If the cell value exist in the uniqueValues collection object, then remove it from the uniqueValues collection object.
                 If cellValueExist = True Then
                 
                     uniqueValues.Remove (existItemIndex)
                     
                 ' Else add the cell value to the collection object.
                 Else
                 
                     uniqueValues.Add cellValue, CStr(cellValue)
                     
                 End If
                     
                 
              End If
          
          End If
           
       Next
       
       ' Assign the collection object's size to the function name to return the unique value's count.
       Count_Unique_Value = uniqueValues.Count
           
   End Function

2. The above VBA function will count the unique values in the provided cell range, you can see the code comments for the detailed explanation.
3. To use it, input the formula =Count_Unique_Value(A2:A6) in cell B6 and press the enter key, then it will display the number 3 in cell B6.

3. How To Count Distinct Values in Excel Using VBA.

1. Input the below source code in the VBA module file to define the function Count_Distinct_Value.

   Function Count_Distinct_Value(cellRange As Range, ignoreBlanks As Boolean) As Integer
   
       ' Define a variable to save a cell value.
       Dim cellValue As Variant
       
       ' Define a collection variable to save distince cell values.
       Dim distinctValues As New Collection
       
       ' The below code will make this user defined function recalculate when any cell of the worksheet caculate.
       ' If do not call the below code Application.Volatile, then this user defined function will only recaculate when the input parameter is changed.
       Application.Volatile
       
       ' Ignore errors and execute to the next line of code.
       On Error Resume Next
       
       ' Loop in the cell range.
       For Each cellValue In cellRange
   
           ' If user ignore blank cell.
           If ignoreBlanks = True Then
           
               ' If the cell value is not empty.
               If IsEmpty(cellValue) = False Then
       
                  ' Add the cell value to the collection object.
                  ' key is the cell object, and the value is the cell string.
                  distinctValues.Add cellValue, CStr(cellValue)
           
               End If
           Else
              
              ' If not ignore empty cell, then add the cell value to the distinct collection object.
              distinctValues.Add cellValue, CStr(cellValue)
              
           End If
           
       Next
       
       ' Assign the collection object's size to the function name to return the distinct value's count.
       Count_Distinct_Value = distinctValues.Count
           
   End Function
   

2. The above function will count the distinct value in the provided Excel cell range.
3. The first parameter is the Excel cell range.
4. The second parameter is a bool value, True means it will ignore the blank cell, and False means it will not ignore the blank cell.
5. Input the formula =Count_Distinct_Value(A2:A6, TRUE) in cell C6 and press the enter key, then it will show the number 4 in cell C6.