Rifat Hassan
To count the unique values in a column is one of the major features that’s used quite often while working in both Excel and VBA. In this article, I’ll show you how you can count the number of unique values in a column in a worksheet using Excel VBA.
How to Count Unique Values in a Column with Excel VBA (Quick View)
Sub Number_of_Values_that_Appear_Only_Once_1()
Set Rng = Range("C4:C15")
Count = 0
For i = 1 To Rng.Rows.Count
   If Application.WorksheetFunction.CountIf(Rng, Rng.Cells(i, 1)) = 1 Then
       Count = Count + 1
   End If
Next i
MsgBox Count
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
How to Count Unique Values in a Column with Excel VBA
Here we’ve got a data set with a list of all the countries that won the ICC Cricket World Cup starting from 2015 to 2019.
Our objective today is to count the number of unique countries from this data set that won the world cup.
While counting the unique values, one may need to count two things, the values that appear exactly once, and the values that appear at least once.
We’ll count both the values in 3 ways possible.
Method 1: Count Unique Values in a Column Using the COUNTIF Function of Excel in VBA (Counting the Values That Appear Exactly Once)
We can use the COUNTIF function of Excel in VBA to count the unique values in a column that appears only once.
The COUNTIF function returns the number of times a value appears in a column.
So we can use it to find out the number of times a value appears in a column. And if it’s 1, we’ll include it in our counting.
Also, as COUNTIF is an Excel function, not a VBA function, we’ve to use Application.WorksheetFunction property of VBA to use it in VBA.
To count the unique values that appear only once in the column C4:C15, the VBA code will be:
⧠VBA Code:
Sub Number_of_Values_that_Appear_Only_Once_1()
Set Rng = Range("C4:C15")
Count = 0
For i = 1 To Rng.Rows.Count
If Application.WorksheetFunction.CountIf(Rng, Rng.Cells(i, 1)) = 1 Then
Count = Count + 1
End If
Next i
MsgBox Count
End Sub
⧠Output:
Run this code. It’ll return the number of unique teams that won the world cup only once, which is 3 in this case (Pakistan, Sri Lanka, and England).
Read More: Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)
Method 2: Count Unique Values in a Column Using Two For-Loops in VBA (Again Counting the Values That Appear Exactly Once)
We can also count the values that appear only once in a column using two for-loops. This is an inverse process. That is, we’ll take each value in the column and examine whether it appears anywhere else in the column or not.
If it appears, then we’ll decrease 1 from the total number of elements of the column. Thus we’ll decrease the number of values that appear more than once from the total number of values in the column.
The remaining will be the number of unique values that appear only once.
So the complete VBA code will be (For counting unique values in the column C4:C15):
⧠VBA Code:
Sub Number_of_Values_that_Appear_Only_Once_2()
Set Rng = Range("C4:C15")
Count = Rng.Rows.Count
For i = 1 To Rng.Rows.Count
  For j = 1 To Rng.Rows.Count
    If j <> i And Rng.Cells(i, 1) = Rng.Cells(j, 1) Then
      Count = Count - 1
      Exit For
    End If
  Next j
Next i
MsgBox Count
End Sub
⧠Output:
Run this code. It’ll again return the number of unique teams that won the world cup only once, which is 3 in this case (Pakistan, Sri Lanka, and England).
Read More: How to Count Unique Values Based on Criteria in Another Column in Excel
Similar Readings:
- How to Count Unique Names in Excel (5 Methods)
- Count Unique Values with Criteria by SUMPRODUCT in Excel
- Excel Formula Count Unique Values (3 Easy Ways)
Method 3: Count Unique Values in a Column That Appear At Least Once in Excel VBA
Finally, we’ll develop a VBA code to count the unique values in a column that appear at least once.
The VBA code to count the unique values in the column C4:C15 will be:
 ⧠VBA Code:
Sub Count_Values_That_Appear_At_Least_Once()
Set Rng = Range("C4:C15")
Dim Used_Values() As Variant
ReDim Used_Values(0)
Count = 0
Match = 0
For i = 1 To Rng.Rows.Count
  For j = LBound(Used_Values) + 1 To UBound(Used_Values)
    If Rng.Cells(i, 1) = Used_Values(j) Then
      Match = Match + 1
      Exit For
    End If
  Next j
  If Match = 0 Then
    Count = Count + 1
    ReDim Preserve Used_Values(Count)
    Used_Values(Count) = Rng.Cells(i, 1)
  End If
  Match = 0
Next i
MsgBox Count
End Sub
⧠Output:
Run this code. It’ll return the number of unique teams that won the world cup at least once, which is 6 in this case.
Read More: How to Count Unique Values in Filtered Column in Excel (5 Methods)
Things to Remember
Here I’ve used the range C4:C15 in all the codes mentioned here. You can change it according to your need.
Conclusion
So these are the ways to count unique values in a column with VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
