Delete rows if cell value is greater than a specific time

Rackette

New Member
Joined
Jul 2, 2019
Messages
37
Hello again!
Hopefully, today was a pleasant day for everyone. Not a perfect one, necessarily, but at least a pleasant one. :)
I thought this would be an easy thing to find, but I can't seem to find any code that will do what I need.

My spreadsheet is only 7 columns by about 300 or 400 rows, so how long it takes to run is not a huge issue.
The 4th column is a date/time column and is in the format like: 9/3/2019 12:01:00 AM
In this date/time column the date will not change throughout the column, BUT the TIME WILL VARY...so all rows will be for the same day but the times will change.
My header row is row 1.
Records start on row 2.
The number of rows is dynamic.

I need to delete all rows where the TIME in column 4 is "less than 0645"

As always, any and all help is appreciated!

-Christine
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try this

Code:
Sub Delete_Rows()
    Dim lr As Long, i As Long, a, r As Range, exists As Boolean
    Application.ScreenUpdating = False
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Set r = Range("A" & lr + 1)
    a = Range("D2:D" & lr)
    For i = 1 To UBound(a)
      If TimeValue(a(i, 1)) < TimeValue("06:45:00") Then Set r = Union(r, Range("A" & i + 1))
    Next i
    r.EntireRow.Delete
End Sub
 
Upvote 0
Dante, thank you for responding!
When I run that, I get a "run-time error", "Type Mismatch" here: If TimeValue(a(i, 1)) < TimeValue("06:45:00") Then
On that line of code, only the part I've pasted in here is highlighted as the error.

-Christine
 
Upvote 0
In column D you must have date and time, example:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:130.22px;" /><col style="width:157.78px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A9</td><td >No</td><td style="color:#0000ff; text-decoration:underline; ">damor@gmail</td><td style="text-align:right; ">09/03/2019 07:01 AM</td><td >E9</td><td >F9</td><td >G9</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A10</td><td >No</td><td style="color:#0000ff; text-decoration:underline; ">damor@hotmail</td><td style="text-align:right; ">09/03/2019 08:01 AM</td><td >E10</td><td >F10</td><td >G10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A11</td><td >B11</td><td >C11</td><td style="text-align:right; ">09/03/2019 09:01 AM</td><td >E11</td><td >F11</td><td >G11</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A12</td><td >No</td><td style="color:#0000ff; text-decoration:underline; ">damor@yahoo</td><td style="text-align:right; ">09/03/2019 10:01 AM</td><td >E12</td><td >F12</td><td >G12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >A13</td><td >B13</td><td >C13</td><td style="text-align:right; ">09/03/2019 11:01 AM</td><td >E13</td><td >F13</td><td >G13</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A14</td><td >B14</td><td >C14</td><td style="text-align:right; ">09/03/2019 12:01 PM</td><td >E14</td><td >F14</td><td >G14</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >A15</td><td >B15</td><td >C15</td><td style="text-align:right; ">09/03/2019 01:01 PM</td><td >E15</td><td >F15</td><td >G15</td></tr></table>

Must be excel dates. Or how do you have the data?
 
Upvote 0
What displays in the cell is the time like 0645. What displays up in the formula bar is like: 9/19/19 6:45:00 AM
 
Upvote 0
something in the column other than a date?
In which row start data?
 
Upvote 0
Hi Rackette,

Try this while on the sheet with the data (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Code:
Option Explicit
Sub DeleteRows()

    Dim rngMyCell As Range
    Dim rngDel As Range
    Dim dteMyTime As Date
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
        If Len(rngMyCell) > 0 Then
            If TimeSerial(Hour(rngMyCell), Minute(rngMyCell), 0) < TimeSerial(6, 45, 0) Then
                If rngDel Is Nothing Then
                    Set rngDel = Cells(rngMyCell.Row, "D")
                Else
                    Set rngDel = Union(rngDel, Cells(rngMyCell.Row, "D"))
                End If
            End If
        End If
    Next rngMyCell
               
    'If the 'rngDelRange' range has been set, then...
    If Not rngDel Is Nothing Then
        '...delete the row(s) from it.
        rngDel.EntireRow.Delete xlShiftUp
    'Else...
    Else
        '...inform the user that no rows were deleted as _
        there were no matching criteria within the dataset.
        MsgBox "There were no rows deleted as no there were no entries matched the desired criteria.", vbExclamation, "Delete Row Editor"
    End If
    
    Application.ScreenUpdating = True

End Sub

Robert
 
Upvote 0
Dante, I"m sorry I was unable to answer your question sooner and ended up wasting your time.

Trebor76, your code works just like I need it to.

My thanks to both of you! :)

-Christine
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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