You can use the following basic syntax to count the number of unique values in a range using VBA:
Sub CountUnique()
Dim Rng As Range, List As Object, UniqueCount As Long
Set List = CreateObject("Scripting.Dictionary")
'count unique values in range A2:A11
For Each Rng In Range("A2:A11")
If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
Next
'store unique count
UniqueCount = List.Count
'display unique count
MsgBox "Count of Unique Values: " & UniqueCount
End Sub
This particular example counts the number of unique values in the range A2:A11 and then displays the count in a message box.
The following example shows how to use this syntax in practice.
Example: Count Unique Values in Range Using VBA
Suppose we have the following list of basketball team names in Excel:
Suppose we would like to count the number of unique team names in the range A2:A11.
We can create the following macro to do so:
Sub CountUnique()
Dim Rng As Range, List As Object, UniqueCount As Long
Set List = CreateObject("Scripting.Dictionary")
'count unique values in range A2:A11
For Each Rng In Range("A2:A11")
If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
Next
'store unique count
UniqueCount = List.Count
'display unique count
MsgBox "Count of Unique Values: " & UniqueCount
End Sub
When we run this macro, we receive the following output:
The message box tells us that there are 5 unique team names.
We can verify that this is correct by manually identifying each of the unique team names:
- Mavs
- Heat
- Nets
- Warriors
- Kings
There are indeed 5 unique team names.
Note: To count the number of unique values in a different range, simply change A2:A11 in the For Each loop to a different range.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Cells with Specific Text
VBA: How to Count Number of Rows in Range
VBA: How to Write COUNTIF and COUNTIFS Functions
