Excel VBA: Count Unique Values in a Column (3 Methods)

Overview of the 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

Excel VBA: Count Unique Values in a Column: 3 Methods

We have a data set with a list of all the countries that won the ICC Cricket World Cup between 1975 and 2019. We’ll count unique entires and display values that appear only once or at least once.

Method 1 – Count Unique Values in a Column with the COUNTIF Function in VBA (Counting the Values That Appear Exactly Once)

• Use the following code:

⧭ 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).

Method 2 – Count Unique Values in a Column Using Two For-Loops in VBA (Again Counting the Values That Appear Exactly Once)

This is an inverse process. We’ll take each value in the column and examine whether it appears anywhere else in the column. If it does, 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 remainder will be the number of unique values that appear only once.

Here’s the code.

⧭ 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 three in this case (Pakistan, Sri Lanka, and England).

Method 3 – Count Unique Values in a Column That Appear At Least Once in Excel VBA

The VBA code 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 have won the World Cup at least once, which is 6 in this case.

Things to Remember

We’ve used the range C4:C15 in all the codes mentioned here. You can change it for your table.

Download the Practice Workbook

<< Go Back to Learn Excel