remove duplicates from sheet2

[hsolanki]

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.

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

 

[Fluff]

That works for me, are you sure that the rows are exactly the same.

 

[hsolanki]

Hi Fluff i have two different workbooks and it works on one of them however it does not work on the 2nd workbook for reason and yet almost everything its the same. below are the screeshot for example whereby you can see it wont remove from sheet2 which is green.

there is a same information on sheet 3 and then sheet2 (which it meant remove the duplicate from sheet2 in green header) but it does not remove.

 

[Fluff]

Whilst they may look the same they probably are not.
Check for leading/trailing spaces, capitalisation & make sure that the dates are both real dates & not text & that none of them contain a time element.

 

[hsolanki]

Hi Fluff no there are actually dates which it is copied from sheet 2 to sheet 3 and then when you refreshes the button it should remove the duplicates from sheet2 which in this case it is not for some unknown reason. i even done the format cell and set as dates

 

[Fluff]

Changing the format does change the underlying values.
If you format the cells as number, do you get 5 figure numbers with no decimals?

 

[hsolanki]

Hi This is was it comes up as when i changed it to both sheet to numbers

44245.00

 

[Fluff]

That looks fine, have you checked for capitalisation & leading trailing spaces?

 

[hsolanki]

Yh i have also checked for capitalisation and trailing spaces it is all okay.

 

[Fluff]

Ok, try setting both ur1 & ur2 like

   With Worksheets("EmailsSent")
      Set ur1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Rows
   End With