Delete rows except the last one with same numerical value

[JOHNQ777]

Hi All

I need a MACRO that will delete all the rows based on a value except the last row with that particular value. The values are in Col B [ Please see attached Wrkbook ]. I have highlighted the row that should not be deleted [ Yellow ].

Thanks in advance.

[Paul_Hossler]

Since row 12 and row 77 (1-1 and 2-1) are both yellow to keep, I'm assuming that the Column A value is also involved and that the definition of 'last' starts over when column A changes




Option Explicit

Sub DeleteAllButLast()
Dim iLastRow As Long, iRow As Long



Application.ScreenUpdating = False

With ActiveSheet
iLastRow = .Cells(1, 2).End(xlDown).Row

For iRow = 1 To iLastRow - 1
If .Cells(iRow, 2).Value = .Cells(iRow + 1, 2).Value Then .Cells(iRow, 2).Value = True
Next iRow

On Error Resume Next
.Columns(2).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0
End With

Application.ScreenUpdating = False
End Sub