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

Counting 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

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

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

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

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

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.

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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

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. Don’t forget to visit our site ExcelDemy for more posts and updates.

<< Go Back to Count | Unique Values | Learn Excel