Delete rows if cell value is greater than a specific time

[Rackette]

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

 

[DanteAmor]

Try this

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

 

[Rackette]

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

 

[DanteAmor]

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?

 

[Rackette]

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

 

[Rackette]

And yes, column "D" contains the date and time.

 

[DanteAmor]

something in the column other than a date?
In which row start data?

 

[Trebor76]

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):

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

 

[Rackette]

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

 

[Trebor76]

Thanks for letting us know and you're welcome