Finding Unique Values In Multiple Columns and Removing Them

Hello everyone,

I think I'm making an easy task into a difficult one here. I want to use a macro (no advanced filter or conditional formatting) to compare cells in one range against cells in four other columns and remove the unique names from the four columns. Essentially this will be scrubbing an old roster of names against a new updated version.

I've tried to do this using a "find duplicates" macro but I'm having trouble deleting the old column and replacing it with the new non-unique range. The pastespecial action errors out. I'm assuming this isn't the best approach. Any and all help is appreciated.


Sub Compare()
Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
xTitleId = "Remove Terminated Associates"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Critical Roles Range :", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)

Application.ScreenUpdating = False
For Each Rng1 In Range1
xValue = Rng1.Value
For Each Rng2 In Range2
If xValue = Rng2.Value Then
If outRng Is Nothing Then
Set outRng = Rng1
Else
Set outRng = Application.Union(outRng, Rng1)
End If
End If
Next
Next
outRng.Select
Selection.Copy

Range1.ClearContents
Range1.PasteSpecial Paste:=xlPasteValues

Application.ScreenUpdating = True
End Sub