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

J

JofFrey

New Member
Joined
Apr 3, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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 dateSpeed
16/03/2024 07:51:36 good37 km/h
16/03/2024 07:51:4614 km/h
16/03/2024 07:51:5640 km/h
16/03/2024 07:52:0646 km/h
16/03/2024 07:52:1672 km/h
16/03/2024 07:52:2664 km/h
16/03/2024 07:52:36 good37 km/h
16/03/2024 07:52:4672 km/h
16/03/2024 07:52:5664 km/h
16/03/2024 07:53:0759 km/h
16/03/2024 07:53:1644 km/h
16/03/2024 07:53:2714 km/h
16/03/2024 07:53:37 good26 km/h
16/03/2024 07:53:4761 km/h
16/03/2024 07:53:5772 km/h
16/03/2024 07:54:0657 km/h
16/03/2024 07:54:1663 km/h
16/03/2024 07:54:2661 km/h
16/03/2024 07:54:3650 km/h
16/03/2024 07:54:46 good57 km/h
16/03/2024 07:54:5652 km/h
16/03/2024 07:55:0638 km/h
16/03/2024 07:55:1650 km/h
16/03/2024 07:55:2640 km/h
16/03/2024 07:55:3646 km/h
16/03/2024 07:55:46 good50 km/h
16/03/2024 07:55:5661 km/h
16/03/2024 07:56:0670 km/h
16/03/2024 07:56:1661 km/h
16/03/2024 07:56:2753 km/h
16/03/2024 07:56:3631 km/h
16/03/2024 07:56:46 good42 km/h
16/03/2024 07:56:5664 km/h
16/03/2024 07:57:0661 km/h
16/03/2024 07:57:1653 km/h
16/03/2024 07:57:2646 km/h
16/03/2024 07:57:36 good0 km/h
16/03/2024 07:57:46 good0 km/h
16/03/2024 07:57:57 good0 km/h
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
JofFrey said:
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 dateSpeed
16/03/2024 07:51:36 good37 km/h
16/03/2024 07:51:4614 km/h
16/03/2024 07:51:5640 km/h
16/03/2024 07:52:0646 km/h
16/03/2024 07:52:1672 km/h
16/03/2024 07:52:2664 km/h
16/03/2024 07:52:36 good37 km/h
16/03/2024 07:52:4672 km/h
16/03/2024 07:52:5664 km/h
16/03/2024 07:53:0759 km/h
16/03/2024 07:53:1644 km/h
16/03/2024 07:53:2714 km/h
16/03/2024 07:53:37 good26 km/h
16/03/2024 07:53:4761 km/h
16/03/2024 07:53:5772 km/h
16/03/2024 07:54:0657 km/h
16/03/2024 07:54:1663 km/h
16/03/2024 07:54:2661 km/h
16/03/2024 07:54:3650 km/h
16/03/2024 07:54:46 good57 km/h
16/03/2024 07:54:5652 km/h
16/03/2024 07:55:0638 km/h
16/03/2024 07:55:1650 km/h
16/03/2024 07:55:2640 km/h
16/03/2024 07:55:3646 km/h
16/03/2024 07:55:46 good50 km/h
16/03/2024 07:55:5661 km/h
16/03/2024 07:56:0670 km/h
16/03/2024 07:56:1661 km/h
16/03/2024 07:56:2753 km/h
16/03/2024 07:56:3631 km/h
16/03/2024 07:56:46 good42 km/h
16/03/2024 07:56:5664 km/h
16/03/2024 07:57:0661 km/h
16/03/2024 07:57:1653 km/h
16/03/2024 07:57:2646 km/h
16/03/2024 07:57:36 good0 km/h
16/03/2024 07:57:46 good0 km/h
16/03/2024 07:57:57 good0 km/h
Click to expand...
I mean retain the first record where the start date is equal to or bigger than 1 minute and delete anything in between those two dates. Except if the Speed is 0 km/h, those always have to be retained.
 
Upvote 0
Hi, so maybe something like this could help you.

Important to note that the macro runs "once" and will delete unwanted rows based on your "formula" above (in terms of time).
It will take longer to run, based on number of rows that get added each time.

You can run it several times and it will overwrite good lines (I mean keep them), and delete the new unwanted values that get added in the meantime ..
eg. You can run it, and it will take whatever data is added at that point. Leave it another minute, and you'll have another 6 rows added (according to you). Next time you run it, it will take those 6 new rows into consideration also.

Results I got as follows on your sample data.
Book1
ABCDE
1Start dateSpeed
216/03/2024 07:5137 km/hgood
316/03/2024 07:5237 km/hgood
416/03/2024 07:5326 km/hgood
516/03/2024 07:5457 km/hgood
616/03/2024 07:5550 km/hgood
716/03/2024 07:5642 km/hgood
816/03/2024 07:570 km/hgood
916/03/2024 07:570 km/hgood
1016/03/2024 07:570 km/hgood
Sheet1


(sorry I forgot to add, I removed "good" from your data and added it into Col "E" just for reference...)

VBA Code: 
Sub keep_good()

Dim lr, x As Long

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

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
       
    If Range("C" & x).Value - good_val > 0.000694444 Or Left(Range("D" & x).Value, 1) = 0 Then
        good_val = Range("c" & x).Value
    Else
        Range("C" & x).EntireRow.Delete
        x = x - 1
    End If

Next x

End Sub

cheers
Rob
 
Upvote 0
RobP said:
Hi, so maybe something like this could help you.

Important to note that the macro runs "once" and will delete unwanted rows based on your "formula" above (in terms of time).
It will take longer to run, based on number of rows that get added each time.

You can run it several times and it will overwrite good lines (I mean keep them), and delete the new unwanted values that get added in the meantime ..
eg. You can run it, and it will take whatever data is added at that point. Leave it another minute, and you'll have another 6 rows added (according to you). Next time you run it, it will take those 6 new rows into consideration also.

Results I got as follows on your sample data.
Book1
ABCDE
1Start dateSpeed
216/03/2024 07:5137 km/hgood
316/03/2024 07:5237 km/hgood
416/03/2024 07:5326 km/hgood
516/03/2024 07:5457 km/hgood
616/03/2024 07:5550 km/hgood
716/03/2024 07:5642 km/hgood
816/03/2024 07:570 km/hgood
916/03/2024 07:570 km/hgood
1016/03/2024 07:570 km/hgood
Sheet1


(sorry I forgot to add, I removed "good" from your data and added it into Col "E" just for reference...)

VBA Code: 
Sub keep_good()

Dim lr, x As Long

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

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
      
    If Range("C" & x).Value - good_val > 0.000694444 Or Left(Range("D" & x).Value, 1) = 0 Then
        good_val = Range("c" & x).Value
    Else
        Range("C" & x).EntireRow.Delete
        x = x - 1
    End If

Next x

End Sub

cheers
Rob
Click to expand...
Hello Rob, thank you for the help however this results in a type mismatch...
I have changed the 0 to "0 km/h" because that is the value in the cell. All the values in both columns are of the "General" type so no Date, Time, Special of Custom format type.
1727170642412.png
1727170642412.png
 

Attachments

  • 1727170596279.png
    1727170596279.png
    23.5 KB · Views: 0
Upvote 0
Hi,

so this line in my original code:

VBA Code: 
Left(Range("D" & x).Value, 1) = 0

The "left" command is looking at only the first digit ie. the zero, so what you have done won't work..

You can try :

VBA Code: 
Range("D" & x).Value = "0 Km/h"
(removing the Left portion of the code.
Rob
 
Upvote 0
RobP said:
Hi,

so this line in my original code:

VBA Code: 
Left(Range("D" & x).Value, 1) = 0

The "left" command is looking at only the first digit ie. the zero, so what you have done won't work..

You can try :

VBA Code: 
Range("D" & x).Value = "0 Km/h"
(removing the Left portion of the code.
Rob
Click to expand...
That didn't change the type mismatch error though. I have tried your suggestion with removing the Left command and keeping the "0 km/h" but with the same result.
1727171598420.png
 
Upvote 0
I think it has to do something with your column C being of another type than mine. Mine is in a General format while yours I guess is in Customs seeing your column data doesn't show the seconds.
 
Upvote 0
Hi Rob, I just tested it by copying the data from the table above to a new workbook and removing the "good" from the cells. Column C changed type "Custom" not showing the seconds and then your code works. However, could you make it work while column C is in General format? 🫣

1727172715199.png
 
Upvote 0
Hi,

I've tried to make my original code fail, but so far I cannot. my format was "custom" as I did paste your data in from above - but if I just change that formatting to "general" for each column, it still works .. even if time/date appear as values ...

I cannot find a way to get your date/time data into a column and show it as "General" .. Excel just won't let me for some reason..

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

VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
199
Tara_A_Data
T
D
  • Solved
highs and lows
2
Replies
15
Views
387
felixstraube
F
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
551
sharky12345
sharky12345
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
598
Alex Blakenburg
A

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top