Using VBA to Count Duplicates in a Range in Excel - 4 Methods

Method 1 – Embed VBA to Count Duplicate Values in a Range in Excel

In the following dataset, Column B contains random numbers. D5 displays 2473.

Search this number in B5:B15 and store the result in E5.

Steps:

Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.

In the code window, click Insert -> Module.

Enter the following code in the code window.

Sub CountDuplicates()
    Dim iSheet As Worksheet
    Set iSheet = Worksheets("Duplicates")
    iSheet.Range("E5") = iSheet.Application.WorksheetFunction.CountIf(iSheet.Range("B5:B15"), iSheet.Range("D5"))
End Sub

Press F5 or select Run -> Run Sub/UserForm. You can also click the small Play icon to run the macro.

This is the output.

E5 displays the count (3) of duplicate values in B5:B15.

Method 2 – Applying a VBA Macro and a formula to Count the Repeated Values

To search the number stored in D5 in B5:B15 and store the result E5, use the COUNTIF function to get the count.

Steps:

Open Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub CountDuplicatesWithFormula()
Dim iSheet As Worksheet
Set iSheet = Worksheets("Formula")
'to count the number of times the value in cell "D5" is stored in range "B5:B9"
iSheet.Range("E5").Formula = "=COUNTIF(B5:B15,D5)"
End Sub

Run the code.

You will get the count (3) of duplicate values in B5:B15 in E5.

Method 3 – Calculate Duplicates in a specified Order with a VBA Macro in Excel

To extract the duplicate values in a separate column in a specific order:

Steps:

Open Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Sub CountDuplicatesInOrder()
    Dim iSheet As Worksheet
    'reference worksheet(change "Order" according to your sheet name)
    Set iSheet = Worksheets("Order")
        'count only positive numbers in specified range
        For i = 5 To 15
        'store the count in column C
        iSheet.Range("C" & i) = Application.WorksheetFunction.CountIf(iSheet.Range(iSheet.Cells(5, 2), iSheet.Cells(i, 2)), iSheet.Cells(i, 2))
        Next i
End Sub

Run the code.

Number 2473 is found in B7 for the first time: column C shows 1. The number is found again in B10: C10 displays the duplicate count, 2. The number is found in B13 for the third time; C13 holds the duplicate count: 3.

Related Content: How to Count Duplicate Values Only Once in Excel

Method 4 – Applying VBA to Count Duplicates and Show the Result in a MsgBox in Excel

To count the duplicate values of a dataset and display the result in the VBA dialog box MsgBox:

Steps:

Open Visual Basic Editor in the Developer tab and Insert a Module.
Enter the following code.

Option Explicit
Sub CountDuplicatesMsgbox()
    Dim iHelper As Range
    Dim iNum As Long
        'reference worksheet(change "MsgBox" according to your sheet name)
        With Worksheets("MsgBox")
        'we need to set a "helper" range to store unique identifiers
        Set iHelper = .UsedRange.Resize(, 1).Offset(, .UsedRange.Columns.count)
            'reference from column B row 4 (header) to last not empty cell
            With .Range("B4", .Cells(.Rows.count, 1).End(xlUp))
            'copy unique identifiers to defined "helper" range
            iHelper.Value = .Value
            'remove duplicates from copied identifiers
            iHelper.RemoveDuplicates Columns:=1, Header:=xlYes
            'count duplicates from the difference between original reference numbers and unique ones
            iNum = .SpecialCells(xlCellTypeConstants).count - iHelper.SpecialCells(xlCellTypeConstants).count
            End With
        'clear defined "helper" range
        iHelper.ClearContents
        End With
    MsgBox "There are " & iNum & " duplicate numbers"
End Sub