Find and delete

[Guest]

Hi, have a workbook with 2 spreadsheets, first one is the master list (with
unique references), second one is an extract (containing duplicated data).
Can anyone help me with a way of looking at the unique references on sheet 2,
finding the duplicate reference on sheet 1 and deleting the row from sheet 1??
Any help much appreciated
Philip

 

[Otto Moehrbach]

Philip
Is "Sheet 2" the "Master" list?
What column(s) do you want to look at to determine if the listings are the
same? Do you want to delete from the Master list or from the other sheet?
HTH Otto

 

[Guest]

Sheet 1 is the Master list (of accounts), sheet 2 is the list of paid
accounts - these rows need to be removed from the Master list (sheet 1). The
column with the the unique references is J on both sheets.
Thanks
Philip

 

[Otto Moehrbach]

Philip
This macro should do what you want. Note that I chose to name your
Sheet1 "One" and your Sheet 2 "Two". I assumed that your data started in
row 2. Change these parameters to suit your data. HTH Otto
Sub DeleteDupRows()
Dim RngJSht2 As Range
Dim RngJSht1 As Range
Dim i As Range
Application.ScreenUpdating = False
Sheets("Two").Select
Set RngJSht2 = Range("J2", Range("J" & Rows.Count).End(xlUp))
With Sheets("One")
Set RngJSht1 = .Range("J2", .Range("J" & Rows.Count).End(xlUp))
End With
For Each i In RngJSht2
On Error Resume Next
RngJSht1.Find(What:=i.Value,
LookAt:=xlWhole).EntireRow.Delete
On Error GoTo 0
Next i
Application.ScreenUpdating = True
End Sub