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

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 

Download Practice Workbook

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

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)

Similar Readings:

• Count Duplicates in Excel Pivot Table (2 Easy Ways)
• How to Count Duplicate Values in Multiple Columns in Excel (6 Ways)
• How to Count Duplicates Based on Multiple Criteria in Excel
• VBA to Count Duplicates in Range in Excel (4 Methods)
• Count the Order of Occurrence of Duplicates in Excel (4 Methods)

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:

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:

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

• Excel Count Number of Occurrences of Each Value in a Column
• How to Count Duplicate Values Only Once in Excel (3 Ways)
• Count Occurrences Per Day in Excel (4 Quick Ways)
• How to Count Duplicate Rows in Excel (4 Methods)
• Count Repeated Words in Excel (11 Methods)