Using VBA to compare 2 columns with another 2 columns

[JOhnG2]

Hi all,
I'm looking to compare surname and first name columns in one worksheet with surname and first name columns in another sheet. If there is a match it's deleted from the first file only. The user enters the range of data to be inputted.
So far i've been able to work on one column but i'm struggling to work with 2. Any help is appreciated. My code is below.

Sub Compare()
Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Range1 :", Type:=8)
Set Range2 = Application.InputBox("Range2:", 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.EntireRow.Delete
Application.ScreenUpdating = True
End Sub

 

[igold]

Hi John,

I got a little confused with your post. I get the part about comparing the surname and first name from columns on one sheet to columns on another sheet. I got lost on the part about the user enters the range of data to be inputted. Are you saying the user in going input a certain name to look for on the two sheets or what...

igold

 

[JOhnG2]

Hi John,

I got a little confused with your post. I get the part about comparing the surname and first name from columns on one sheet to columns on another sheet. I got lost on the part about the user enters the range of data to be inputted. Are you saying the user in going input a certain name to look for on the two sheets or what...

igold

Sorry I mentioned the range just to try and explain my code a little. Don't worry about it.
If you were able to help comparing the surname and first name from columns on one sheet to columns on another sheet it would be great.

 

[tonyyy]

JOhnG2,

One approach to comparing 2 columns with another 2 columns is to use the Concatenate function, or its operator "&"...

[COLOR=#a9a9a9]Sub Compare()
Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
[/COLOR]Dim xValue As String[COLOR=#a9a9a9]

[/COLOR]'Set Range1 = Application.Selection[COLOR=#a9a9a9]
Set Range1 = Application.InputBox("Range1:", Type:=8)
Set Range2 = Application.InputBox("Range2:", Type:=8)
Application.ScreenUpdating = False

For Each Rng1 In Range1
    [/COLOR]xValue = Rng1.Value & Rng1.Offset(0, 1).Value[COLOR=#a9a9a9]
    For Each Rng2 In Range2
        [/COLOR]If xValue = Rng2.Value & Rng2.Offset(0, 1).Value Then[COLOR=#a9a9a9]
            If outRng Is Nothing Then
                Set outRng = Rng1
                Else
                Set outRng = Application.Union(outRng, Rng1)
            End If
        End If
    Next
Next

outRng.EntireRow.Delete
Application.ScreenUpdating = True
End Sub[/COLOR]

The code assumes the surname and first names columns are adjacent, and uses the Offset method to specify the adjacent range.

Cheers,

tonyyy

 

[JOhnG2]

Tonyyy,
That's great. Sometimes you don't see the obvious when it's staring you in the face. Code works a treat. Many thanks.
John

 

[Peter_SSs]

Whilst it most likely won't be an issue for you here, I would advise against direct concatenation like that to do such a comparison.
To demonstrate, consider a case where the names are all in UPPER case

In Range 1 is the name "JO" "EWING". In Range 2 is the very different name "JOE" "WING".
Concatenating them as suggested turns them both into "JOEWING" meaning they would test the same when they are not.

That might be unlikely and perhaps upper/lower cases may also help you out but in general, to avoid the possibility I would suggest a concatenation like

Rng1.Value & "|" & Rng1.Offset(0, 1).Value