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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,679
Office Version
2007
Platform
Windows
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
 

Rackette

New Member
Joined
Jul 2, 2019
Messages
37
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,679
Office Version
2007
Platform
Windows
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

New Member
Joined
Jul 2, 2019
Messages
37
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,679
Office Version
2007
Platform
Windows
something in the column other than a date?
In which row start data?
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,481
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
 

Rackette

New Member
Joined
Jul 2, 2019
Messages
37
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
 

Forum statistics

Threads
1,085,475
Messages
5,383,903
Members
401,863
Latest member
Sisma

Some videos you may like

This Week's Hot Topics

Top