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

Related Content: How to Ignore Blanks and Count Duplicates in Excel

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

• 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

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

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

• Run the code.

A dialog box shows a message with the total count of duplicate values.

Read More: How to Count Repeated Words in Excel

Download Workbook

Download the free practice Excel workbook.

## Related Articles

<< Go Back to Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF