VBA Deleting rows - criteria x2

[ListersPants]

Hi,

I have inserted a table with a before and after scenario.

If a Model (Column B) has a 'Transmission Start' and a 'Transmission End' (Column A) then I want to delete the whole row for that model for the row which contains 'Transmission Start'. So that it leaves the row for that model which has 'Transmission End'. In the below example Models 356 and 873 are deleted as they have a 'Transmission Start' and 'End'.

I have cobbled the below together but it simply deletes any row which contains 'Transmission Start'.

With ActiveSheet
    .AutoFilterMode = False
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Transmission Start"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

My data:

Before
Type	Model
Transmission start <colgroup><col width="139"></colgroup><tbody> </tbody>	356
Transmission start <colgroup><col width="139"></colgroup><tbody> </tbody>	873
Transmission end <colgroup><col width="139"></colgroup><tbody> </tbody>	356
Transmission start	896
Transmission end <colgroup><col width="139"></colgroup><tbody> </tbody>	873
Transmission start	231
Transmission end	111
After
Transmission end	356
Transmission start	896
Transmission end	873
Transmission start	231
Transmission end	111

<tbody>
</tbody>

 

[Alan_P]

Hi ListersPants,

Give this a go, see if it works for you...

Sub RemoveDupeTrans()
LastRw = Range("A" & Rows.Count).End(xlUp).Row
  
For i = LastRw To 1 Step -1
    If UCase(Range("A" & i).Value) = "TRANSMISSION END" Then
        Srch = Range("B" & i).Value
        For j = LastRw To 1 Step -1
            If UCase(Range("A" & j).Value) = "TRANSMISSION START" And Range("B" & j).Value = Srch Then
                Rows(j).Delete
                Cntr = Cntr + 1
                GoTo Out
            End If
        Next
Out:
    End If
Next
MsgBox (Cntr & " Rows Deleted")
End Sub

Hope this helps,
Cheers,
Alan.

 

[ListersPants]

Thank you Alan, that is excellent and works perfectly.