# How to remove repeating time intervals?



## Waimea (Monday at 11:42 AM)

17:58:282020-08-02LocationDim LevelCHANGE17:57:092020-08-02xOffOn to Off17:06:452020-08-02xOnOn16:45:362020-08-02xOffOn to Off08:15:032020-08-02xOnOn02:43:522020-08-02xOffOff02:33:502020-08-02xOffOn to Off02:05:032020-08-02xOnOn01:05:022020-08-02xOffOff00:56:452020-08-02xOffOff00:28:452020-08-02xOffOff00:05:022020-08-02xOffOff23:05:032020-08-01xOffOff18:09:302020-08-01xOffOff16:43:462020-08-01xOffOn to Off08:15:032020-08-01xOnOn03:49:462020-08-01xOffOff03:46:092020-08-01xOffOn to Off02:05:022020-08-01xOnOn01:05:022020-08-01xOffOff00:05:032020-08-01xOffOff23:05:022020-07-31xOffOff23:00:032020-07-31xOffOff17:16:502020-07-31xOffOff17:16:502020-07-31xOffOff

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​


----------



## iggydarsa (Monday at 1:56 PM)

Not sure what you meant by the "Off" criteria but the code below removes the duplicate minutes and keeps only one (Assuming your times are on column A)

```
Sub RemoveMin()
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = lr To 2 Step -1
        For j = i - 1 To 1 Step -1
            If Minute(Range("A" & j)) = Minute(Range("A" & i)) Then
                Range("A" & j).EntireRow.Delete
                Exit For
            End If
        Next j
    Next i
End Sub
```


----------



## Waimea (Monday at 2:08 PM)

Hi, thank you for your reply!

I have times in column A but I get a type mismatch when I run your code.

I want to delete all the minutes. So 23:05:02, 00:05:03, 01:05:04 all get deleted.

Is this possible?


----------



## iggydarsa (Monday at 2:20 PM)

Maybe there is a better way but I can do it by using a helper column.
I'm assuming column F is blank (if not you can change it to another column)

give this a try


```
Sub RemoveMin()
    On Error Resume Next
    lr = Range("A" & Rows.Count).End(xlUp).Row

'Identify duplicates
    For i = 1 To lr - 1
        For j = i + 1 To lr
            If Range("F" & i).Value <> "x" Then
                If Minute(Range("A" & j)) = Minute(Range("A" & i)) Then
                    Range("F" & i).Value = "x"
                    Range("F" & j).Value = "x"
                End If
            End If
        Next j
    Next i
   
'Remove duplicates
    For i = lr To 1 Step -1
        If Range("F" & i).Value = "x" Then
            Range("F" & i).EntireRow.Delete
        End If
    Next i
End Sub
```


----------



## Waimea (Monday at 2:25 PM)

Hi again,

thank you for your quick reply and your code.

I did a quick test and of 3000 rows only 60 are left after running your macro.

Is there something I am doing wrong? I want to keep all times that are not xx:05:xx.


----------



## iggydarsa (Monday at 2:46 PM)

Oh my bad, I thought you wanted to remove all the duplicate times.
This make the job even easier.

If you only wanna remove minute "05"s then try this:

```
Sub Remove05()
    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
End Sub
```


----------



## Waimea (Monday at 2:52 PM)

Hi, thank you again.

Now your code works great! 

Maybe you can take a swing on deleting the unwanted off's?


----------



## iggydarsa (Monday at 2:53 PM)

I'm not clear what you need for the Off situation. You will need to elaborate more


----------



## Waimea (Monday at 2:59 PM)

Hi,

I'll try to elaborate as good as I can.

Time + 1hTimeDateTime2LocationDim LevelStartEndLengthStatus21:02:4420:02:4425-08-201920:02:44 25/08/2019xOff0:00Off to Off18:39:0717:39:0725-08-201917:39:07 25/08/2019xOff18:3921:022:23On to Off17:51:5916:51:5925-08-201916:51:59 25/08/2019xOn17:5118:390:47On13:02:0612:02:0625-08-201912:02:06 25/08/2019xOff13:0217:514:49Off to Off09:23:108:23:1025-08-201908:23:10 25/08/2019xOff9:2313:023:38On to Off07:20:306:20:3025-08-201906:20:30 25/08/2019xOn7:209:232:02On03:05:032:05:0325-08-201902:05:03 25/08/2019xOff3:057:204:15Off to Off02:05:021:05:0225-08-201901:05:02 25/08/2019xOff2:053:051:00Off to Off01:05:030:05:0325-08-201900:05:03 25/08/2019xOff1:052:050:59Off to Off00:59:1623:59:1624-08-201923:59:16 24/08/2019xOff0:591:050:05On to Off00:47:4323:47:4324-08-201923:47:43 24/08/2019xOn0:470:590:11On00:05:0323:05:0324-08-201923:05:03 24/08/2019xOff0:050:470:42Off to Off21:00:4720:00:4724-08-201920:00:47 24/08/2019xOff21:000:053:04Off to Off18:30:3417:30:3424-08-201917:30:34 24/08/2019xOff18:3021:002:30On to Off17:48:0216:48:0224-08-201916:48:02 24/08/2019xOn17:4818:300:42On05:02:264:02:2624-08-201904:02:26 24/08/2019xOff5:0217:4812:45Off to Off03:05:032:05:0324-08-201902:05:03 24/08/2019xOff3:055:021:57Off to Off02:05:031:05:0324-08-201901:05:03 24/08/2019xOff2:053:051:00Off to Off01:05:030:05:0324-08-201900:05:03 24/08/2019xOff1:052:051:00Off to Off00:05:0323:05:0323-08-201923:05:03 23/08/2019xOff0:051:051:00Off to Off21:05:5120:05:5123-08-201920:05:51 23/08/2019xOff21:050:052:59Off to Off20:15:2519:15:2523-08-201919:15:25 23/08/2019xOff20:1521:050:50Off to Off13:04:5912:04:5923-08-201912:04:59 23/08/2019xOff13:0420:157:10Off to Off09:20:268:20:2623-08-201908:20:26 23/08/2019xOff9:2013:043:44On to Off07:25:266:25:2623-08-201906:25:26 23/08/2019xOn7:259:201:55On03:05:032:05:0323-08-201902:05:03 23/08/2019xOff3:057:254:20Off to Off02:05:031:05:0323-08-201901:05:03 23/08/2019xOff2:053:051:00Off to Off01:05:030:05:0323-08-201900:05:03 23/08/2019xOff1:052:051:00Off to Off

I want to delete Off to Off, but only if there are more then 1 "Off to Off". Se last column and marked with red color.

Does this make sense?

EDIT: In this table, the xx:05:xx minutes are not removed yet.


----------



## iggydarsa (Monday at 3:06 PM)

You tell me if I got it; change the column "E" in the code to your "Status" column and give it a try and lets see...


```
Sub RemoveOff()
    lr = Range("E" & Rows.Count).End(xlUp).Row
    cnt = WorksheetFunction.CountIf(Range("E:E"), "Off to Off")
    If cnt > 1 Then
        For i = lr To 1 Step -1
            If Range("E" & i).Value = "Off to Off" Then
                Range("E" & i).EntireRow.Delete
            End If
        Next i
    End If
End Sub
```


----------



## Waimea (Monday at 11:42 AM)

17:58:282020-08-02LocationDim LevelCHANGE17:57:092020-08-02xOffOn to Off17:06:452020-08-02xOnOn16:45:362020-08-02xOffOn to Off08:15:032020-08-02xOnOn02:43:522020-08-02xOffOff02:33:502020-08-02xOffOn to Off02:05:032020-08-02xOnOn01:05:022020-08-02xOffOff00:56:452020-08-02xOffOff00:28:452020-08-02xOffOff00:05:022020-08-02xOffOff23:05:032020-08-01xOffOff18:09:302020-08-01xOffOff16:43:462020-08-01xOffOn to Off08:15:032020-08-01xOnOn03:49:462020-08-01xOffOff03:46:092020-08-01xOffOn to Off02:05:022020-08-01xOnOn01:05:022020-08-01xOffOff00:05:032020-08-01xOffOff23:05:022020-07-31xOffOff23:00:032020-07-31xOffOff17:16:502020-07-31xOffOff17:16:502020-07-31xOffOff

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 (Monday at 3:14 PM)

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 (Monday at 3:24 PM)

[deleted]


----------



## iggydarsa (Monday at 3:28 PM)

```
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 (Monday at 11:09 PM)

Hi iggydarsa,

thank you for your help and for your code.

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


----------

