Microsoft Office Forums - View Single Post - [Solved] Compare two lists and delete rows that are NOT the same

Carchee · 04-23-2018, 01:17 PM

I pulled this code from a youtube video and it compares two lists and removes duplicates from one of them. However I would like to change the code so it removes non-duplicates. Here is the code:

Code:

Sub RemoveDupsBetweenLists()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim C1row As Long
Dim C2row As Long
Dim C2TotalRows As Long
Dim CustID As String
Dim NoDups As Long
Set sht1 = Worksheets("Customers 1")
Set sht2 = Worksheets("Customers 2")
sht2.Activate
C2TotalRows = Application.CountA(Range("A:A"))
C1row = 2
Do While sht1.Cells(C1row, 2).Value <> ""
CustID = sht1.Cells(C1row, 2).Value
    For C2row = 2 To C2TotalRows
        If CustID = Cells(C2row, 2).Value Then
 
            sht1.Activate
            Rows(C1row).Delete
            NoDups = NoDups + 1
            C1row = C1row - 1
            sht2.Activate
            Exit For
 
        End If
 
    Next
 
    C1row = C1row + 1
Loop
 
MsgBox NoDups & " Duplicates were removed"
End Sub

Now what I think I need to do is just change the code from

Code:

 If CustID = Cells(C2row, 2).Value Then

to

Code:

 If CustID <> Cells(C2row, 2).Value Then

However when I change the = to <>, it deletes all the rows instead of just those that don't match. Can anyone critique this code