Table of Contents
VBA (Visual Basic for Applications) is a programming language that can be used to count unique values in a range of cells in Microsoft Excel. It uses a combination of loops, conditions, and functions to identify and count the unique values found in a range of cells, and can be customized to provide the desired output. The code can be written manually, or by using the Visual Basic Editor in Excel.
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.