Help deleting certain rows

[bradyj7]

Hi there,

Thanks for taking the time read this.

I have five columns of data being logged from a vehicle - Time, speed, difference in time (dt), acceleration and micro trip. You can see that speed is recorded every 1-6 seconds. A micro trip is the speed time profile between two successive stops. A micro trip starts at each zero value for speed. However sometimes due to poor GPS signals there can be a time lapse of up to 30 seconds before a speed value is recorded - for example at 12:21:45.

I'm looking for some help to do the following. If more than 10 seconds passes without speed being logged (i.e a value for dt greater than 10), then delete all the rows belonging to that micro trip.

So for the example below, I would like it to delete all rows between 12:20:19 and 12:22:05 inclusive.

Some additional info: The number of rows and and the number micro trips can vary. The files are quite large so there could be more than one occurrence per file so it would need to loop through dt until it is empty.

Thanks for the in advance and I hope I've explained it clearly, but if not please let me know.

John

Time	      Speed	dt	Acc    	Micro Trips
 12:19:49	0	6	-2.50	        1
 12:19:52	11	3	3.67	
 12:19:54	24	2	6.50	
 12:20:00	44	6	3.33	
 12:20:05	44	5	0.00	
 12:20:10	34	5	-2.00	
 12:20:15	11	5	-4.60	
 12:20:19	0	4	-2.75         2
 12:20:25	0	6	0.00	
 12:20:29	0	4	0.00	
 12:20:35	20	6	3.33	
 12:20:39	13	4	-1.75	
 12:20:45	34	6	3.50	
 12:20:49	43	4	2.25	
 12:20:55	47	6	0.67	
 12:21:00	48	5	0.20	
 12:21:04	49	4	0.25	
 12:21:09	50	5	0.20	
 12:21:13	52	4	0.50	
 12:21:14	46	1	-6.00	
 12:21:45	37	31	-0.29	
 12:21:46	24	1	-13.00	
 12:21:47	22	1	-2.00	
 12:21:48	33	1	11.00	
 12:21:49	31	1	-2.00	
 12:21:50	27	1	-4.00	
 12:21:54	29	4	0.50	
 12:21:59	34	5	1.00	
 12:22:05	31	6	-0.50	
 12:22:09	0	4	-7.75 	   3

 

[Sektor]

Sub RemoveBigDt()

    Dim lastRow As Long
    
    lastRow = Range("A1").End(xlDown).Row
    Range("A1:A5").AutoFilter Field:=3, Criteria1:=">10"
    Range("A1:E" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Range("A1:A5").AutoFilter 'Turn off Autofilter

End Sub