How to remove repeating time intervals?

[Waimea]

17:58:28	2020-08-02	Location	Dim Level	CHANGE
17:57:09	2020-08-02	x	Off	On to Off
17:06:45	2020-08-02	x	On	On
16:45:36	2020-08-02	x	Off	On to Off
08:15:03	2020-08-02	x	On	On
02:43:52	2020-08-02	x	Off	Off
02:33:50	2020-08-02	x	Off	On to Off
02:05:03	2020-08-02	x	On	On
01:05:02	2020-08-02	x	Off	Off
00:56:45	2020-08-02	x	Off	Off
00:28:45	2020-08-02	x	Off	Off
00:05:02	2020-08-02	x	Off	Off
23:05:03	2020-08-01	x	Off	Off
18:09:30	2020-08-01	x	Off	Off
16:43:46	2020-08-01	x	Off	On to Off
08:15:03	2020-08-01	x	On	On
03:49:46	2020-08-01	x	Off	Off
03:46:09	2020-08-01	x	Off	On to Off
02:05:02	2020-08-01	x	On	On
01:05:02	2020-08-01	x	Off	Off
00:05:03	2020-08-01	x	Off	Off
23:05:02	2020-07-31	x	Off	Off
23:00:03	2020-07-31	x	Off	Off
17:16:50	2020-07-31	x	Off	Off
17:16:50	2020-07-31	x	Off	Off

Hi, I have time data and I want to remove reapeating time intervals, at 02:05:03, at 01:05:02 etc. It could also be 23:05:13 et.

The minute is always 05 but the hour and the seconds varies but I still want to remove the corresponding rows.

I also want to delete multiple Off to Off in the change column. If the change column has Off, Off, Off I want to remove the corresponding rows.

If you have any questions, I'll try to answer as best as I can.

Grateful for your assistance!

I am looking for a VBA solution!

02:05:03​

 

[Waimea]

Hi again,

thank you for your reply.

I think your code deletes all the "Off to Off"?

If there are 3 "Off to Off" next to each other I want to remove 2 of them. If there are 2, I want to remove 1 of them.

If there are 5 "Off to Off" I want to delete 4 of them, leaving the last one on top.

 

[iggydarsa]

[deleted]

 

[iggydarsa]

Sub RemoveAll()
    'Remove 05s
    On Error Resume Next
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If Minute(Range("A" & i)) = 5 Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i
    
    'Remove Offs
    lr = Range("E" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        If Range("E" & i).Value = "Off to Off" And Range("E" & i - 1).Value = "Off to Off" Then
            Range("E" & i).EntireRow.Delete
        End If
    Next i
End Sub

 

[Waimea]

Hi iggydarsa,

thank you for your help and for your code.

The last code is great and you nailed it, solved it, aced it!