how to delete rows that have cells with matching contents in column 'B'

[nwnexus]

I have a spreadsheet that I need a vba solution for.

If a cell in column 'B' matches a cell in another row in column 'B', then delete both rows.

Thanks for any help!

 

[John Davis]

I have a spreadsheet that I need a vba solution for.

If a cell in column 'B' matches a cell in another row in column 'B', then delete both rows.

Thanks for any help!

Can your data be sorted?

 

[MrKowz]

Try:

Public Sub CleanColB()
Dim i   As Long, _
    rng As Range, _
    LR  As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    Set rng = Range("B1:B" & i - 1).Find(Range("B" & i).Value, LookIn:=xlValues, searchdirection:=xlPrevious)
    If Not rng Is Nothing Then
        Rows(i).Delete Shift:=xlUp
        rng.EntireRow.Delete Shift:=xlUp
    End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub