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

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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF