Find and copy non-matching cells to another worksheet

I would like to compare cells in Worksheet 2 against Worksheet 1.

First check for matching cells in range A for both Worksheets 1 and 2.

Next, if there are no matches, check for matching cells in range B for both Worksheets 1 and 2, otherwise check the next cell in range A.

If there are no matches as well, copy these non-matching cells in ranges A and B in Worksheet 2 to a new worksheet, Worksheet 3.


Here are my worksheets' layout:

Worksheet 1 -

1.png

Worksheet 2 -

2.png

Worksheet 3 -

3.png


From the screenshot of Worksheet 2, only the rows highlighted in yellow should be copied to Worksheet 3 since its values in both range A and B cannot be found in Worksheet 1's corresponding range A and B.


Here is my code so far, but it's only checking range A for both Worksheets 1 and 2:

```
Dim Cl As Range, Rng As Range, Dic As Object

Set Dic = CreateObject("scripting.dictionary")

With Dic
For Each Cl In wsk1.Range("A2", wsk1.Range("A" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Empty
Next Cl
For Each Cl In wsk2.Range("A2", wsk2.Range("A" & Rows.Count).End(xlUp))
If Not .Exists(Cl.Value) Then
If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
End If
Next Cl
End With

If Not Rng Is Nothing Then
Rng.EntireRow.Copy wsk3.Range("A" & Rows.Count).End(xlUp)
End If
```

Many thanks!

Hi TropicalMagic,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

You're welcome and thank you for marking thread as solved and the rep