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 Get_Count_Unique_Values macro is selected in the window and run by pressing button.+ keys,
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 :
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
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.
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
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 :
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