VBA: delete all rows where value (time) is less than 1 minute

[JofFrey]

Hello, I have to analyse a file where almost every 10 seconds a record is generated which is almost impossible to do. Therefore I would like to keep records where the date & time (Column C Start date) difference does not exceed 1 minute. I also want to keep the records where the speed (Column D) = "0 km/h".
So in the example underneath I would like to keep the records where I have added " good". I have no idea how to do this so basically I am doing it manually. However if one of you Excel wizards could create a a function in VBA that deletes those records or marks them in any way that I could filter them out it would mean a real help. I am talking about 2500-6000 each day so you know what I have to go through and how many coffees that would take.
BTW if I do C8-C2= 0,000694444 (so 1 minute equals 0,000694444).

Start date	Speed
16/03/2024 07:51:36 good	37 km/h
16/03/2024 07:51:46	14 km/h
16/03/2024 07:51:56	40 km/h
16/03/2024 07:52:06	46 km/h
16/03/2024 07:52:16	72 km/h
16/03/2024 07:52:26	64 km/h
16/03/2024 07:52:36 good	37 km/h
16/03/2024 07:52:46	72 km/h
16/03/2024 07:52:56	64 km/h
16/03/2024 07:53:07	59 km/h
16/03/2024 07:53:16	44 km/h
16/03/2024 07:53:27	14 km/h
16/03/2024 07:53:37 good	26 km/h
16/03/2024 07:53:47	61 km/h
16/03/2024 07:53:57	72 km/h
16/03/2024 07:54:06	57 km/h
16/03/2024 07:54:16	63 km/h
16/03/2024 07:54:26	61 km/h
16/03/2024 07:54:36	50 km/h
16/03/2024 07:54:46 good	57 km/h
16/03/2024 07:54:56	52 km/h
16/03/2024 07:55:06	38 km/h
16/03/2024 07:55:16	50 km/h
16/03/2024 07:55:26	40 km/h
16/03/2024 07:55:36	46 km/h
16/03/2024 07:55:46 good	50 km/h
16/03/2024 07:55:56	61 km/h
16/03/2024 07:56:06	70 km/h
16/03/2024 07:56:16	61 km/h
16/03/2024 07:56:27	53 km/h
16/03/2024 07:56:36	31 km/h
16/03/2024 07:56:46 good	42 km/h
16/03/2024 07:56:56	64 km/h
16/03/2024 07:57:06	61 km/h
16/03/2024 07:57:16	53 km/h
16/03/2024 07:57:26	46 km/h
16/03/2024 07:57:36 good	0 km/h
16/03/2024 07:57:46 good	0 km/h
16/03/2024 07:57:57 good	0 km/h

 

[JofFrey]

MrExcel_deleteTimDif.xls
	A	B	C	D
1			Start date	Speed
2			16/03/2024 07:51:36	37 km/h
3			16/03/2024 07:51:46	14 km/h
4			16/03/2024 07:51:56	40 km/h
5			16/03/2024 07:52:06	46 km/h
6			16/03/2024 07:52:16	72 km/h
7			16/03/2024 07:52:26	64 km/h
8			16/03/2024 07:52:36	37 km/h
9			16/03/2024 07:52:46	72 km/h
10			16/03/2024 07:52:56	64 km/h
11			16/03/2024 07:53:07	59 km/h
12			16/03/2024 07:53:16	44 km/h
13			16/03/2024 07:53:27	14 km/h
14			16/03/2024 07:53:37	26 km/h
15			16/03/2024 07:53:47	61 km/h
16			16/03/2024 07:53:57	72 km/h
17			16/03/2024 07:54:06	57 km/h
18			16/03/2024 07:54:16	63 km/h
19			16/03/2024 07:54:26	61 km/h
test

 

[RobP]

when I use the above, it still pastes in as Custom. For my previous code just posted, I forced all to be "Text" in General formatting.

So that latest code should work for you.

Rob

 

[JofFrey]

If its plain text in your file, maybe this can work :

Sub keep_good()

Dim lr, x As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
good_val = TimeValue(Right(Range("c2"), 8))

For x = 3 To lr 'start from row3 as we first value is always "good".
  
    If Range("C" & x) = "" Then Exit For 'check for blank rows to quit macro
   
       new_val = TimeValue(Right(Range("C" & x), 8))
    If new_val - good_val > 0.000694444 Or Left(Range("D" & x).Value, 1) = 0 Then
        good_val = TimeValue(Right(Range("c" & x).Value, 8))
    Else
        Range("C" & x).EntireRow.Delete
        x = x - 1
    End If

Next x

End Sub

Hi Rob, thank you very much, that worked as a charm, just had to declare "good_val" and "new_val" as a variable and perfect!
If you're ever in Belgium feel free to pass by for a cup of coffee, you saved me from drinking liters.
Thanks again!

 

[RobP]

Great, glad it worked finally - thanks for the feedback.

Yes I plan to be there next year ! so won't forget..

Rob