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.

VBA find duplicates in Column

VBA code to Remove Duplicates in ListBox

Here 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: Procedure

Following 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: Examples

The 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 Column

Please 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 Column

Starting 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 
Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: January 19th, 2015Categories: VBATags: ,

Share This Story, Choose Your Platform!

About the Author: PNRao

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

8 Comments

  1. Sohail May 16, 2015 at 3:49 PM

    Great explanation…. U People really God ‘s Own Creation

    Thanks A Lot

    Sohail Imran

  2. Suresh August 25, 2016 at 8:57 PM

    Hi Team,

    I have a question : i have given the some count in one of excel cell, now i need to find that data input cell in vba coding. could you please advise how to write this coding.

    Regards,
    Suresh

  3. siamak mahdikar February 1, 2017 at 2:51 AM

    Hi ,
    Thanks a lot ,
    I am from Iran , so I do not speak english very vell . excuse me .
    I have one question .
    How I can change column B to each coloumn ? Can I set this macro to any column ? for example run macro ic column E and F ?
    please help me .

  4. iel February 5, 2017 at 2:38 AM

    what if i want only find duplicates for all the same that i input to the textbox in userform

    i hope this problem have a solution

    thank you in advance

  5. Kurt N. February 27, 2020 at 1:48 AM

    Try this: (will highlight duplicates in yellow)

    Sub FindDuplicateValuesInRange()
    Dim cl, rng As Range

    Set rng = Range(“A1:A” & Cells(Rows.Count, “A”).End(xlUp).Row)

    For Each cl In rng
    If WorksheetFunction.CountIf(rng, cl.Value) > 1 Then
    cl.Interior.Color = vbYellow
    End If
    Next
    End Sub

  6. Bhojraj Timsina September 20, 2020 at 9:50 AM

    How to prevent dublicating values in this user entries form?

    Private Sub CommandButton1_Click()
    Dim y As Worksheet
    Dim X As Long
    Set y = Sheet1

    X = y.Range(“A” & Rows.Count).End(xlUp).Row
    With y
    .Cells(X + 1, “A”).Value = TextBox1.Value
    .Cells(X + 1, “B”).Value = TextBox2.Value
    .Cells(X + 1, “C”).Value = TextBox3.Value
    .Cells(X + 1, “D”).Value = TextBox6.Value
    .Cells(X + 1, “E”).Value = TextBox7.Value
    .Cells(X + 1, “F”).Value = TextBox8.Value
    .Cells(X + 1, “G”).Value = TextBox22.Value
    .Cells(X + 1, “I”).Value = TextBox23.Value
    .Cells(X + 1, “J”).Value = TextBox24.Value
    .Cells(X + 1, “K”).Value = TextBox25.Value
    .Cells(X + 1, “L”).Value = TextBox26.Value
    .Cells(X + 1, “M”).Value = TextBox27.Value
    .Cells(X + 1, “N”).Value = TextBox28.Value
    .Cells(X + 1, “O”).Value = TextBox29.Value
    .Cells(X + 1, “P”).Value = TextBox30.Value
    .Cells(X + 1, “Q”).Value = TextBox31.Value
    .Cells(X + 1, “R”).Value = TextBox32.Value
    .Cells(X + 1, “S”).Value = TextBox33.Value
    .Cells(X + 1, “T”).Value = TextBox34.Value
    .Cells(X + 1, “H”).Value = TextBox36.Value

    End With
    ‘clear tha data
    TextBox1.Text = “”
    TextBox2.Text = “”
    TextBox3.Text = “”
    TextBox6.Text = “”
    TextBox7.Text = “”
    TextBox8.Text = “”
    TextBox23.Text = “”
    TextBox24.Text = “”
    TextBox25.Text = “”
    TextBox26.Text = “”
    TextBox27.Text = “”
    TextBox28.Text = “”
    TextBox29.Text = “”
    TextBox30.Text = “”
    TextBox31.Text = “”
    TextBox32.Text = “”
    TextBox33.Text = “”
    TextBox34.Text = “”
    TextBox22.Text = “”
    TextBox36.Text = “”
    Unload Me
    UserForm2.Show
    End Sub

  7. Marta November 13, 2020 at 9:40 PM

    Hi, i have data set with name and few entries under one name, table will be populated vis user form but i will need have a code that will run checks under each name to see if have more then 3 entries in whole data base and notify user of this . Any ideas?

  8. Subhash N November 20, 2020 at 10:58 PM

    Hi
    I have requirement where in column A I have list of names say
    A
    B
    C
    A
    A
    Here I need to change the duplicate names to
    A
    B
    C
    A_1
    A_2
    Like above can someone please help me

Leave A Comment