# Thread: Remove Duplicates in Two Workbooks

1. Thank you to all who replied. I think I may have left out part of the equation. I am editing my question.

You have two (2) workbooks. Both workbooks have some of the same data in multiple columns in rows. Using the same column in each workbook, if there are duplicates from each workbook, delete the duplicate data in the entire row in the second workbook.

Thanks again!

2. This one clears data in the cell only.
Code:
```Sub deleteDupes2Books()
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
Dim rKeep As Range, lRow As Long, i As Long, j As Integer
Set wb1 = Workbooks("Book1.xls")
Set wb2 = Workbooks("Book2.xls")
Set ws1 = wb1.Sheets(1)
Set ws2 = wb2.Sheets(1)
Application.ScreenUpdating = False
lRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
j = 1
On Error Resume Next
Do Until ws1.Cells(2, j) = ""
Set rKeep = ws1.Range(ws1.Cells(2, j), ws1.Cells(Rows.Count, j).End(xlUp))
For i = lRow To 2 Step -1
If Application.WorksheetFunction.Match(ws2.Cells(i, j), rKeep, 0) > 0 Then
If Err.Number <> 1004 Then
ws2.Cells(i, j).ClearContents
End If
Err.Clear
End If
Next
j = j + 1
Loop
Application.ScreenUpdating = True
End Sub```
This one deletes the entire row if there is a duplicate.
Code:
```Sub deleteDupes2BooksRows()

Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
Dim rKeep As Range, lRow As Long, i As Long, j As Integer
Set wb1 = Workbooks("Book1.xls")
Set wb2 = Workbooks("Book2.xls")
Set ws1 = wb1.Sheets(1)
Set ws2 = wb2.Sheets(1)
Application.ScreenUpdating = False
lRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
j = 1
On Error Resume Next
Do Until ws1.Cells(2, j) = ""
Set rKeep = ws1.Range(ws1.Cells(2, j), ws1.Cells(Rows.Count, j).End(xlUp))
For i = lRow To 2 Step -1
If Application.WorksheetFunction.Match(ws2.Cells(i, j), rKeep, 0) > 0 Then
If Err.Number <> 1004 Then
ws2.Cells(i, j).EntireRow.Delete
lRow = lRow - 1
End If
Err.Clear
End If
Next
j = j + 1
Loop
Application.ScreenUpdating = True
End Sub```

3. To: Mbarron:

Thank you again. Is this a Macro or Visual Basic?

4. Macros are a method of writing a VB procedure.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•