Solved: Delete Rows-However keep the first found Row

[nick99]

In the attached excel sheet i need to find rows with similar data in coulmn B.
Then i want to delete all the rows with similar data, except for the first found rows.

That means only rows 2, 3, 4 & 11 should be the end result.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim mpLastRow As Long
Dim mpSelected As Range


With ActiveSheet

mpLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To mpLastRow
If Application.CountIf(.Cells(1, TEST_COLUMN).Resize(i), .Cells(i, TEST_COLUMN).Value) > 1 Then
If mpSelected Is Nothing Then
Set mpSelected = .Rows(i)
Else
Set mpSelected = Union(.Rows(i), mpSelected)
End If
End If
Next i

If Not mpSelected Is Nothing Then mpSelected.Delete
End With

End Sub
[/vba]

[nick99]

thanks XLD....works perfect.
is there a book you can recommend to learn codes for excel?