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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>