How to delete row if Date / Time between 05:00 - 20:00
Category: [Excel] Demo Available
How to delete row if Date / Time between 05:00 - 20:00
Requirement:
The user is trying to delete a row if the Date / Time in the format 18/12/2018 05:00:00 if the time is greater than 05:00:00 and less than 20:00:00 to delete the whole row.
Sample
| ID | ti_date |
| 6415 | 18/12/2018 06:17 |
| 4828 | 18/12/2018 07:00 |
| 4828 | 18/12/2018 07:00 |
| 2314 | 18/12/2018 08:00 |
| 2314 | 18/12/2018 08:00 |
| 1193 | 18/12/2018 09:07 |
| 1959 | 18/12/2018 13:22 |
| 1959 | 18/12/2018 13:22 |
| 2260 | 18/12/2018 14:25 |
| 1936 | 18/12/2018 15:22 |
| 1936 | 18/12/2018 15:22 |
| 8714 | 18/12/2018 17:12 |
| 8714 | 18/12/2018 17:12 |
| 8714 | 18/12/2018 17:14 |
| 1379 | 18/12/2018 20:03 |
| 1379 | 18/12/2018 20:03 |
| 5298 | 18/12/2018 20:16 |
| 5298 | 18/12/2018 20:16 |
| 1147 | 18/12/2018 20:16 |
| 5298 | 18/12/2018 20:35 |
| 5298 | 18/12/2018 20:35 |
| 5298 | 18/12/2018 20:38 |
| 5298 | 18/12/2018 20:38 |
| 1147 | 18/12/2018 20:44 |
| 1147 | 18/12/2018 20:45 |
| 1147 | 18/12/2018 20:47 |
| 1147 | 18/12/2018 20:47 |
| 5298 | 18/12/2018 20:50 |
| 5298 | 18/12/2018 20:50 |
| 5298 | 18/12/2018 21:23 |
| 5298 | 18/12/2018 21:23 |
| 6509 | 18/12/2018 22:00 |
| 6509 | 18/12/2018 22:00 |
| 1860 | 18/12/2018 23:23 |
| 1860 | 18/12/2018 23:24 |
| 1656 | 19/12/2018 00:52 |
| 1656 | 19/12/2018 00:52 |
| 4491 | 19/12/2018 05:01 |
| 4491 | 19/12/2018 05:01 |
| 4491 | 19/12/2018 05:04 |
Solution:
Code:
Sub fatpierre()
Application.ScreenUpdating = False
Dim LastRow As Long, x As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For x = LastRow To 2 Step -1
If Hour(Cells(x, 2)) + Minute(Cells(x, 2)) > 5 And Hour(Cells(x, 2)) < 20 Then
Rows(x).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True
End Sub
In order for the above to work:
The date is in column C of the sheet will have to change from:
If Hour(Cells(x, 2)) + Minute(Cells(x, 2)) > 5 And Hour(Cells(x, 2)) < 20 Then
To
If Hour(Cells(x, 3)) + Minute(Cells(x, 3)) > 5 And Hour(Cells(x, 3)) < 20 Then
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy data in VBA from different named workbook each time |
| How to copy 300 rows at a time from one column with 3K rows and convert to csv file |
| How to set a countdown timer to beep under 10 seconds |
| How to use IF function for time calculation |
| How to use VBA code to transpose any copy values X number of times |
| How to use code to insert below and copy the row as many times as the cell contains |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.