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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
That works for me, are you sure that the rows are exactly the same.
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Attachments

  • sheet2.png
    sheet2.png
    21.2 KB · Views: 3
  • Sheet3.jpg
    Sheet3.jpg
    91.8 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
That looks fine, have you checked for capitalisation & leading trailing spaces?
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
124
Office Version
  1. 2010
Platform
  1. Windows
Yh i have also checked for capitalisation and trailing spaces it is all okay.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Ok, try setting both ur1 & ur2 like
VBA Code:
   With Worksheets("EmailsSent")
      Set ur1 = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Rows
   End With
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,427
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