find and delete

[genesisoxygen]

I need a macro that will look in columns A and B and when there is a
match found it will delete that entire row.
Column B doesnt have duplicates however column A does. So if whats in
column B is found in column A I need those rows deleted.
Example:

Column A Column B Column C Column D
Item ID Item ID Item Disc Location Code
SR0512 SR0512 oxygen tank 1
SR0512 SR0513 oxygen tank 10
SR0512 SR0514 oxygen tank 15
SR0512 SR0515 oxygen tank 20
SR0513 wheelchair 1
SR0513 wheelchair 10
SR0514 commode 30
SR0515 vent 35
SR0516 c-pap 40
SR0516 c-pap 45

I would like the outcome to look like this

Column A Column B Column C Column D
Item ID Item ID Item Disc Location Code
SR0516 c-pap 40
SR0516 c-pap 45

 

[JMB]

try:

Sub Test()
Dim rngA As Range
Dim rngCell As Range
Dim rngDelete As Range
Dim varB As Variant

Set rngA = Range("A2:A11")
varB = Range("B2:B11").Value

For Each rngCell In rngA.Cells
If IsNumeric(Application.Match(rngCell.Value, varB, 0)) Then
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireRow.Delete

End Sub