Remove dupes for book1 if exist in book2

paulyt7410

New Member
Joined
Dec 29, 2005
Messages
5
Hello,

New to the board, and just as new when it comes to macros. Would appreciate any guidance.

In book 1 I have the following data:

NAME ADDR1
ABC 1 Main St
DEF 2 Main


In book 2 I have
NAME ADDR1
GHI 3 Main
DEF 2 Main

I want to delete the duplicate row in workbook one, but I do NOT want to add the unique row in book 2 to book 1.

I want book 1 to look like so

NAME ADDR1
ABC 1 Main St

I tried playing around with advanced filter by copying the data from book2 to book1 - but of course that only hides the rows that are dupes - but leaves all the unique data copied from book2.

Thanks in advance!

Paul
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
Try something like this sub(modify to suit you)
first row headers name addr1
data is in col A and B in both the sheets.

Public Sub TEST()
Dim myrange As Range
Worksheets("sheet2").Activate
Set myrange = Range(Range("a2"), Range("A2").End(xlDown))
Dim cell As Range
Dim x As String
Dim c As Range

For Each cell In myrange

x = cell.Value
With Worksheets("sheet1").Range("a2:a65536")
Set c = .Find(what:=x, lookat:=xlWhole)
End With
If Not c Is Nothing Then c.EntireRow.Delete
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,462
Messages
5,572,260
Members
412,451
Latest member
newbie22922792
Top