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:A11ForEach 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.