Matching values in 2 columns
 

Hi, I'll try to make this as clear as i can. I have 2 columns say 'A' and
'B'(A has around 22000 values, the other with around 10) on seperate
worksheets in a workbook. I want a script that will loop through one column
and delete the rows(in column 'A') that don't match with the values in the
other column('B'). Can anyone help me? I'm pretty new to VBA. Thank you

Matching values in 2 columns
 

Sub Redundancy()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If IsError(Application.Match(Cells(i, "A").Value, _
Range("B:B"), 0)) Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Aonghus" wrote in message
...
Hi, I'll try to make this as clear as i can. I have 2 columns say 'A' and
'B'(A has around 22000 values, the other with around 10) on seperate
worksheets in a workbook. I want a script that will loop through one
column
and delete the rows(in column 'A') that don't match with the values in the
other column('B'). Can anyone help me? I'm pretty new to VBA. Thank you

Matching values in 2 columns
 

Thanks a million Bob that worked like a charm. Really quick response as well

"Bob Phillips" wrote:

Sub Redundancy()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If IsError(Application.Match(Cells(i, "A").Value, _
Range("B:B"), 0)) Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Aonghus" wrote in message
...
Hi, I'll try to make this as clear as i can. I have 2 columns say 'A' and
'B'(A has around 22000 values, the other with around 10) on seperate
worksheets in a workbook. I want a script that will loop through one
column
and delete the rows(in column 'A') that don't match with the values in the
other column('B'). Can anyone help me? I'm pretty new to VBA. Thank you