# Excel VBA to Count Duplicates in a Column (A Complete Analysis)

Get FREE Advanced Excel Exercises with Solutions!

While working with VBA in Excel, we often need to count duplicates in a column in a worksheet. In this article, I’ll show you how you can count duplicates in an Excel column through step-by-step analysis including examples involving a Macro and a User-Defined function with proper illustrations.

Excel VBA to Count Duplicates in a Column (Quick View)

``````Sub Count_Duplicates_With_Unique_Values()

Set Rng = Range("B3:B13")

Count = 0
Count2 = 0
Output = ""
Match = False

Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"

For i = 1 To Rng.Rows.Count
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j
If Match = False Then
Count2 = Count2 + 1
ReDim Preserve Elements(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count)
Count = 0
End If
Match = False
Next i

MsgBox Output

End Sub```  ```

## How to Count Duplicates in a Column with Excel VBA (Step by Step Analysis)

Without further delay, let’s move to our main discussion. Here we’ve got a data set with the names of some books. Let’s count the duplicate values from this data set with the help of Excel VBA.

### 1. Developing a Macro to Count Duplicates in a Column with Excel VBA Not Ignoring the Unique Values

First of all, we’ll develop a Macro to count the duplicate values not ignoring the unique values. I am showing you the step-by-step procedure to execute this.

⧪ Step 1: Declaring the Range

The 1st step that we need to accomplish is to declare the column as a range whose duplicate values are to be counted. Here it’s B3:B13. The line of code will be:

``Set Rng = Range("B3:B13")``

⧪ Step 2: Declaring the Other Variables

Then we’ve to declare the other variables. These include two integers, a string, a boolean value, and an array.

The lines of code will be:

``````Count = 0
Count2 = 0
Output = ""
Match = False

Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"``````

⧪ Step 3: Starting a For-Loop to Iterate through Each Element of the Range

Next, we’ll start a for-loop to iterate through each element of the declared range.

The line of code will be:

``For i = 1 To Rng.Rows.Count``

⧪ Step 4: Checking Whether the Duplicates of a Value are Already Counted or Not

Then we’ll iterate through another for-loop to check whether the duplicates of one value are already counted or not.

``````    For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j``````

⧪ Step 5: Counting the Duplicates of a Value

This is the most important step. If the duplicates of a value aren’t already counted, we’ll iterate through another for-loop to count it duplicates.

``````   If Match = False Then
Count2 = Count2 + 1
ReDim Preserve Elements(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count)
Count = 0
End If

Match = False``````

⧪ Step 6: Ending the For-loop and Displaying the Output

Finally, we’ll end the for-loop and display the output.

``````Next i

MsgBox Output``````

Therefore, the complete VBA code will be:

⧭ VBA Code:

``````Sub Count_Duplicates_With_Unique_Values()

Set Rng = Range("B3:B13")

Count = 0
Count2 = 0
Output = ""
Match = False

Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"

For i = 1 To Rng.Rows.Count
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then

Match = True
Exit For
End If
Next j
If Match = False Then
Count2 = Count2 + 1
ReDim Preserve Elements(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count)
Count = 0
End If
Match = False
Next i

MsgBox Output

End Sub`````` ⧭ Output:

Run the code. It’ll display the number of duplicate values of all the elements of column B3:B13. Read More: How to Count Duplicates in Column in Excel (3 Ways)

### 2. Creating a Macro to Count Duplicates in a Column with Excel VBA Ignoring the Unique Values

This time we’ll develop a Macro that’ll display the number of duplicate values of each element of a column ignoring the unique values.

The code is almost the same as the previous one. The only difference is that we’ll display only the values whose number of duplicates is greater than one.

So the VBA code will be:

⧭ VBA Code:

``````Sub Count_Duplicates_Without_Unique_Values()

Set Rng = Range("B3:B13")

Count = 0
Output = ""
Count2 = 0
Match = False

Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"

For i = 1 To Rng.Rows.Count
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j
If Match = False Then
Count2 = Count2 + 1
ReDim Preserve Elements(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
If Count > 1 Then
Output = Output + vbNewLine + Rng.Cells(i, 1) + ": " + Str(Count)
End If
Count = 0
End If
Match = False
Next I

MsgBox Output

End Sub`````` ⧭ Output:

Run the code. It’ll again display the number of the duplicate values of all elements of the range B3:B13, but this time ignoring the unique values. Read More: How to Ignore Blanks and Count Duplicates in Excel (3 Ways)

### 3. Developing a User-Defined Function to Count Duplicates in a Column with Excel VBA

Finally, we’ll develop a User-Defined function to count the duplicate values of a column with the help of VBA, both ignoring and not ignoring the unique values.

The VBA code will be:

⧭ VBA Code:

``````Function Count_Duplicates(Rng As Range, Ignore_Unique As Boolean)

Count = 0

Dim Elements() As Variant
ReDim Elements(0)
Elements(0) = "ExcelDemy"

Dim Duplicates() As Variant
ReDim Duplicates(0)
Duplicates(0) = 3

Count2 = 0
Match = False

For i = 1 To Rng.Rows.Count
For j = LBound(Elements) To UBound(Elements)
If Elements(j) = Rng.Cells(i, 1).Value Then
Match = True
Exit For
End If
Next j
If Match = False Then
Count2 = Count2 + 1
For j = 1 To Rng.Rows.Count
If Rng.Cells(j, 1) = Rng.Cells(i, 1) Then
Count = Count + 1
End If
Next j
ReDim Preserve Elements(Count2)
ReDim Preserve Duplicates(Count2)
Elements(Count2) = Rng.Cells(i, 1).Value
Duplicates(Count2) = Count
Count = 0
End If
Match = False
Next i

Dim Output As Variant
ReDim Output(UBound(Elements) - 1, 1)

If Ignore_Unique = True Then
Count3 = 0
For i = 1 To UBound(Elements)
If Duplicates(i) > 1 Then
Output(Count3, 0) = Elements(i)
Output(Count3, 1) = Duplicates(i)
Count3 = Count3 + 1
End If
Next i
Else
For i = 1 To UBound(Elements)
Output(i - 1, 0) = Elements(i)
Output(i - 1, 1) = Duplicates(i)
Next i
End If

Count_Duplicates = Output

End Function`````` ⧭ Output:

To count duplicates of a column ignoring the unique values, select a range of cells (rows equal to that of the range, and columns 2) and enter this formula:

`=Count_Duplicates(B3:B13,TRUE)` Then press CTRL + SHIFT + ENTER (Array Formula). It’ll return an array consisting of the number of duplicates of all the values, ignoring the unique ones. Similarly, to count not ignoring the unique values, enter:

`=Count_Duplicates(B3:B13,FALSE)` Press CTRL + SHIFT + ENTER (Array Formula) and It’ll return an array consisting of the number of duplicates of all the values, not ignoring the unique ones. Read More: How to Count Duplicates in Two Columns in Excel (8 Methods)

## Conclusion

So this is these are the ways to count duplicates in a column with the help of Excel VBA.  Hope the examples made everything pretty clear for you. Still, if 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.

1. Reply Your code works great and the explanation of how it works was very helpful for me to modify it for my home project. I modified your “Creating a Macro to Count Duplicates in a Column with Excel VBA Ignoring the Unique Values” and it works fantastic. Now I am trying to modify it again for an issue that came up and I cannot figure it out so I am asking for your guidance. Some cells in my sample data have delimiters between entries (a / in my case) and I am trying to modify your code to remove the delimiter and count the entries separately. For example I have entries:
AA
BB
CC
AA/BB
BB/CC
I would like to split those entries with the delimiter and count them with the individual entries, BB + AA/BB + BB/CC = 3, AA + AA/BB = 2, etc. I know that I need to use the Split function but can’t figure out how to implement it with your code.

• Reply Hello BRIAN,

This is Aung, one of the writers/developers in ExcelDmey. I’ve looked into your matter. I understand you want to split the cell values. Kindly send me your excel file at [email protected]

I’ll make the necessary changes and explain them to you.

Good luck. Advanced Excel Exercises with Solutions PDF  