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

Get FREE Advanced Excel Exercises with Solutions!

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

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.

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

## Related Articles Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  