VBA find duplicate values in a column Excel Macros Examples Codes: to find all duplicate records in a column in MS Excel 2003, 2007, 2010, 2013. We will also see the practical VBA example for finding the duplicates in a Column.
50+ Project Management Templates Pack
Excel PowerPoint Word
Advanced Project Plan & Portfolio Template
Business Presentations Templates Pack
20+ Excel Project Management Pack
20+ PowerPoint Project Management Pack
10+ MS Word Project Management Pack
VBA code to Remove Duplicates in ListBoxHere is the Example VBA syntax and Example VBA Macro code to find Duplicates in a Column in Excel. This will help you to know how to find duplicate records in a column using VBA.
VBA find Duplicates in Column: ProcedureFollowing is the VBA Syntax and sample VBA macro command to find duplicates in a Column of Worksheet using VBA. In this method we loop through all the records and identify the duplicates using VBA.
VBA Find Duplicates in a Column: ExamplesThe following Excel VBA macro code is to find duplicate values in a column. This VBA macro will loop through the all the items in the first column and identify the duplicates using Match Spreadsheet Function. Here the logic is, the number is unique if the match index equals to the current processing row number. Otherwise it will be duplicate, it will print the “duplicate” in the second column.
Sub sbFindDuplicatesInColumn() Dim lastRow As Long Dim matchFoundIndex As Long Dim iCntr As Long lastRow = Range("A65000").End(xlUp).Row For iCntr = 1 To lastRow If Cells(iCntr, 1) <> "" Then matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0) If iCntr <> matchFoundIndex Then Cells(iCntr, 2) = "Duplicate" End If End If Next End Sub
Instructions to run the VBA Macro code to find duplicates in a ColumnPlease follow the below steps to execute the VBA code to delete duplicates in Column.
- Step 1: Open any Excel workbook
- Step 2: Press Alt+F11 – This will open the VBA Editor
- Step 3: Insert a code module from then insert menu
- Step 4: Copy the above code and paste in the code module which have inserted in the above step
- Step 5: Enter some data values in Column 1. Make sure that you have some duplicate items in the data for testing purpose
- Now press F5 to Execute the macro and test the code
Now you can observe the code is loop thronging all the items in the column 1. And identifying the duplicates and printing the label as “Duplicate” in the Column B if the value is repeating.
Explained VBA Code to Find Duplicates in A ColumnStarting Macro program and sub procedure to write VBA code to find duplicate records in a Worksheet Column.
- Declaring the lastRow variable as Long to store the last row value in the Column1
- Declaring the variable MatchFoundIndex is to store the match index values of the given value
- Declaring the variable iCntr is to loop through all the records in the column 1 using For loop
- Finding the last row in the Column 1
- looping through the column1
- Checking if the cell is having any item, skipping if it is blank.
- Getting match index number for the value of the cell
- If the match index is not equals to current row number, then it is a duplicate value
- Printing the label in the column B
Here is the commented VBA Macro code, explained the procedure by each statement.
Sub sbFindDuplicatesInColumn_C() 'Declaring the lastRow variable as Long to store the last row value in the Column1 Dim lastRow As Long 'matchFoundIndex is to store the match index values of the given value Dim matchFoundIndex As Long 'iCntr is to loop through all the records in the column 1 using For loop Dim iCntr As Long 'Finding the last row in the Column 1 lastRow = Range("A65000").End(xlUp).Row 'looping through the column1 For iCntr = 1 To lastRow 'checking if the cell is having any item, skipping if it is blank. If Cells(iCntr, 1) <> "" Then 'getting match index number for the value of the cell matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0) 'if the match index is not equals to current row number, then it is a duplicate value If iCntr <> matchFoundIndex Then 'Printing the label in the column B Cells(iCntr, 2) = "Duplicate" End If End If Next End Sub