remove duplicates from sheet2

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi everyone i have an code whereby it will loop between the sheet2 and sheet3 and look for duplicate from sheet3 to sheet2 whereby both sheets have 3 columns. it is currently not working and it will not delete the duplicates from sheet 2 after looping into sheet3.

VBA Code:
Sub RemoveDuplicateRows()
    Dim ur1 As Range, ur2 As Range, dupeRows As Range
    Dim r1 As Range, s1 As String, r2 As Range, s2 As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    

    Set ur1 = Worksheets("EmailsSent").UsedRange.Rows
    Set ur2 = Worksheets("EmailReport").UsedRange.Rows  'Find duplicates from Sheet1 in Sheet2
    
    Sheet2.Unprotect Password:="2020"

    Set dupeRows = ur2(Worksheets("EmailReport").UsedRange.Rows.Count + 1)
    For Each r1 In ur1
        s1 = Join(Application.Transpose(Application.Transpose(r1)))
        For Each r2 In ur2
            s2 = Join(Application.Transpose(Application.Transpose(r2)))
            If s1 = s2 Then
                If Intersect(dupeRows, r2) Is Nothing Then
                    Set dupeRows = Union(dupeRows, r2)
                End If
            End If
        Next
    Next

    dupeRows.EntireRow.Delete
    Sheet2.Protect Password:="2020"
     Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff You are a star. it worked. Thank you once again for all your help :)🙂
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top