Kapat

Useful Macros-1: Get Count Of Unique Values

excel get count of unique values in column  Find Count Of Unique
  Values In Column

Unique and distinct values in a column can be found with the formula, but faster and error-free results are obtained with VBA codes.

In our template, country names are listed as mixed in column A. Some country names are entered multiple times. In this list, we created a procedure to find the number of unique and distinct values (country names) ​​and added it to the code section of Sheet1 in the VBE window.

The Macro window is opened by pressing Alt + F8 keys, Get_Count_Unique_Values ​​macro is selected in the window and run by pressing Run button. excel vba get count of unique values

excel find count of distinct values

We used the scripting dictionary object to find the unique values in the column. The scripting dictionary is a handy way to store unique items via keys and items. Then, when necessary, these stored data can be quickly accessed.

Codes of Get_Count_Unique_Values procedure : excel vba scripting dictionary using

Sub Get_Count_Unique_Values()
 Dim rng As Range

 With CreateObject("Scripting.Dictionary")
   For Each rng In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
   If rng <> Empty Then
      If Not .Exists(rng.Value) Then 'If the item has been added before,it will not be added again.
        .Add rng.Value, Nothing
      End If
   End If
  Next
 Range("C2").Value = .Count
 End With
End Sub

📥 Download sample Excel workbook


  In the other template that we prepared, we found the count of unique values separately as the numeric unique values and other unique (text) values. excel vba scripting dictionary object

We added code snippet to above codes to find numeric unique values.

...
Dim say as Long
If IsNumeric(rng.Value) Then say = say + 1
...
Range("C3").Value = say

📥 Download Sample File


  We have added codes to Worksheet_Change method of the worksheet so that the macro will run automatically without opening the macro window. Get_Count_Unique_Values macro is triggered when there is a change in any cell of column A.
VBA codes that we added to Worksheet_Change method : excel vba worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = [A1] Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Call Get_Count_Unique_Values
End If
End Sub
excel get count of distinct values

📥 Sample file can be downloaded here