PDA

View Full Version : Solved: Delete Rows-However keep the first found Row



nick99
07-26-2007, 02:23 PM
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
07-26-2007, 02:49 PM
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

nick99
07-27-2007, 05:29 AM
thanks XLD....works perfect.
is there a book you can recommend to learn codes for excel?