Excel VBA to Delete Rows Based on Date & Time

browncountry

New Member
Joined
Feb 2, 2019
Messages
13
F
G
1
DATE
TIME
2
2/19/2019
18:00
3
2/19/2019
23:20
4
2/19/2019
3:30
5
2/20/2019
6:30
6
2/20/2019
7:00
7
2/20/2019
8:00

<tbody>
</tbody>

I'm having trouble deleting rows based on date & time. I have a report that exports transaction dates and times in Columns F & G respectively. Time exports as a text field. The idea is to delete rows from the earlier (MIN) date that are less than Time Value 20:00 and all rows from the latter date (MAX) greater than Tim Value 6:30.

I am able to create formulas to mark these rows, but I just don't know how to translate that to VBA code.

These are formulas I created to mark rows to KEEP.
=IF(AND(F2=MIN(F:F),TIMEVALUE(G2)>TIMEVALUE("20:00")),"x","")
=IF(AND(F2=MAX(F:F),TIMEVALUE(G2)<timevalue("6:30")),"x","")


In my example, Rows 3-5 would not be deleted. This comes from and 3rd shift production report so dates vary. That's why I figured MIN and MAX functions could be incorporated. Please help. Thanks!</timevalue("6:30")),"x","")
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
Have you considered:

  1. Creating an Excel Table in a template worksheet that contains:
    1. your formula to flag rows for deletion (that should auto replicate in the Table to cover any additional data rows)
    2. columns for your new data
  2. Paste your new data into the template
  3. Apply filter to "Delete These" column where value = "X"
  4. Select all visible rows in the table (should only be those to delete)
  5. Select "Visible cells only" (F5 [Goto], Special, Visible cells only) >> you can also add a built-in button for this command to your QAT
  6. Delete rows
  7. Clear filter

If you don't have to do this task very often, the above doesn't take much time to do manually, avoiding the need for VBA.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,758
Members
416,202
Latest member
donya ba

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
Top