VBA highlight duplicates in a column Excel Macros Examples Codes

Home/VBA/VBA highlight duplicates in a column Excel Macros Examples Codes

VBA highlight duplicates in a column Excel Macros Examples Codes: to highlight all duplicate records in a column in MS Excel 2003, 2007, 2010, 2013. We will also see the practical VBA example for highlighting the duplicates in a Column.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA code to Highlight Duplicates in Column

Here is the Example VBA syntax and Example VBA Macro code to highlight duplicates in a Column in Excel. This will help you to know how to highlight duplicate records in a column using VBA.

VBA highlight Duplicates in Column: Procedure

Following is the VBA Syntax and sample VBA macro command to highlight duplicates in a Column of Worksheet using VBA. In this method we loop through all the records and identify the duplicates using VBA.

VBA Highlight Duplicates in a Column: Examples

The following Excel VBA macro code is to highlight duplicates 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 highlight column in Yellow.

Sub sbHighlightDuplicatesInColumn()
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) .Interior.Color = vbYellow
End If
End If
Next
End Sub

Instructions to run the VBA Macro code to highlight duplicates in a Column

Please follow the below steps to execute the VBA code to highlight 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 Highlighting the duplicated cell in Yellow color if the values are repeating.

Explained VBA Code to Highlight Duplicates in a Column

Starting Macro program and sub procedure to write VBA code to highlight 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
  • Highlighting 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
  • Highlighting the duplicated cell in Yellow color

Here is the commented code explained to highlight the duplicates in a column:

Sub sbHighlightDuplicatesInColumn_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
'Highlighting 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
'Highlighting the duplicated cell in Yellow color
Cells(iCntr, 2) .Interior.Color = vbYellow
End If
End If
Next
End Sub 
LIMITED TIME OFFER
By |January 19th, 2015|VBA|3 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

3 Comments

  1. Francisco Solorzano June 3, 2015 at 7:01 AM - Reply

    Buenas noches
    Thanks!
    This macro send error:
    Error of compilation
    Error of Sintaxys
    Help me?

    • PNRao June 3, 2015 at 12:32 PM - Reply

      Hi, Good morning!
      Please provide the example file to understand your data.
      Thanks-PNRao!

  2. Jason Valenzuela August 18, 2016 at 12:13 AM - Reply

    Thanks so much, this works great. I was wondering if it’s possible to highlight both/all instances of duplication. This seems to only highlight the duplicate but not the “original”, if that makes sense.

Leave A Comment


Related pages


worksheet.protectsum if formulavlookup syntax in excelinsert a check boxsimple pie chart worksheetsql query for excelvba sql statementexcel create vbavba excel insert rowunprotect excel vba project passworddata validation excel 2010count duplicate values in excelactivesheet.listobjectsexcel dashboards tutorialtcl commands in sql with examplesexcel vba ebook pdfvb scripting interview questions and answers pdfhow to create a tick box in exceldim string vbavba fillvba excel tutorialsvba msgbox new lineexcel delete rowsopen xlsx files in excel 2003vlookup uses examplesinsert rows in excelexcel infographic dashboardexcel ribbon vbahow to create dashboards in excelhow to unlock formulas in excelwhat is a pivot chart in excelvba user formsrecordset access vbaexcel sheet lockms excel basics tutorialfor each sheet in workbookinsert comment excelhow to insert multiple columns in exceladvanced filter in excel 2010pivot table excel vbasas coding for beginnersvba saveas fileformathow to use the lookup function in excel 2010project status report ppt templateinsert button in excel 2007powerpoint dashboard templatesfso deletecapacity model template excelexcel msgbox yes nooption buttons in excelformula for vlookup in excelhow to filter duplicate rows in excelvba excel templatehow do you use vlookup in excel 2010vlookup duplicatesexcel interactive dashboard templateexcel appendvb open file dialogaccess vba queryhow to create data entry form in excel 2007unlock excel sheet passwordmacro to close workbookinsert a checkbox in excel 2010excel macro workbookunprotect sheet excel passwordcells.find excel vbainsertrangehow to filter duplicate rows in excelexcel vba protect worksheetexcel formulas sumifmicrosoft outlook questions and answersmsgbox codehow do i get rid of extra pages in excelselecting a sheet in vbavba quote