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

Excel VBA count duplicates in range

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

This is the output.

result of excel vba count duplicates in range

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:

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

excel vba count duplicates in range with formula

Run the code.

result of excel vba count duplicates in range with formula

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

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

excel vba count duplicates in range according to order

Run the code.

result of excel vba count duplicates in range in order

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

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

excel vba count duplicates in range in msgbox