Macro To Compare Two Excel WorkSheets and Delete Duplicate Rows

Previously, we wrote a macro on comparing two excel worksheet, highlight duplicate entries and copy duplicates to a new worksheet. Some of our readers were looking for a macro that compares two excel worksheets and delete duplicate rows from the first worksheet.

Note: To find out the detailed steps on how to create, run or edit a macro, you may refer here for excel 2003 or here for excel 2007.

Few Points Worth To Note:

  • While creating the Macro, ensure that name of the macro is kept as “compare_delete_duplicate”
  • Worksheet names are considered to be “Sheet1″ and “Sheet2″
  • Duplicate rows are deleted in Sheet1

Hope you find this solution useful, your suggestions, feedback and comments are always welcome.

  1. Hi,
    I have tried this and its not working. I’m not sure why its not working? I have a copied the code for you to look at -

    Sub compare_delete_duplicate()
    CompareWorksheets Worksheets(“Sheet1″), Worksheets(“Sheet2″)
    End Sub

    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
    Dim dupRow As Boolean
    Dim r As Long, c As Integer, m As Integer
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer, lr3 As Long
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
    Dim dupCount As Long

    Application.ScreenUpdating = False
    Application.StatusBar = “Creating the report…”
    Application.DisplayAlerts = True
    With ws1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
    End With
    With ws2.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
    End With
    maxR = lr1
    maxC = lc1
    If maxR < lr2 Then maxR = lr2
    If maxC < lc2 Then maxC = lc2
    DiffCount = 0
    lr3 = 1
    For i = 1 To lr1
    dupRow = True
    Application.StatusBar = “Comparing cells ” & Format(i / maxR, “0 %”) & “…”
    For r = 1 To lr2
    For c = 1 To maxC
    ws1.Select
    cf1 = “”
    cf2 = “”
    On Error Resume Next
    cf1 = ws1.Cells(i, c).FormulaLocal
    cf2 = ws2.Cells(r, c).FormulaLocal
    On Error GoTo 0
    If cf1 <> cf2 Then
    dupRow = False
    Exit For
    Else
    dupRow = True
    End If
    Next c
    If dupRow Then
    dupCount = dupCount + 1
    ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, maxC)).Select
    Selection.Rows(i).EntireRow.Delete
    End If
    Next r
    Next i
    Application.StatusBar = “Formatting the report…”
    m = dupCount
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox m & ” Rows are deleted…!!”, vbInformation, _
    “Compare ” & ws1.Name & ” with ” & ws2.Name
    End Sub

  2. Hi, When running the Macro a message appers (microsoft visual basic) compile error : syntax error and hightlights – If maxR &1t; 1c2 Then maxR = 1r2 do i need to change the code? This will be a great help with sorting work documents out.
    Thank you Richard

  3. Hi, I have tested this and its leaving some of the right information with some of the wrong information, not sure why… I have tried “macro to compare two excel worksheets and copy different rows to new worksheet” and that works it leaves me with the right information.
    Thanks Richard

  4. when there are some rows present in sheet 2 which are not there in sheet 1 then it counts correctly no. of rows to be deleted but did not delete them

  5. when there are some rows present in sheet 2 which are not there in sheet 1 then it counts correctly no. of rows to be deleted but did not delete them…………………………………………………………………….

Leave a Reply to Richard Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">