Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Delete rows if cell value is greater than a specific time

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Delete rows if cell value is greater than a specific time

    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,236
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

    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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

    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

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,236
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

    In column D you must have date and time, example:

     ABCDEFG
    1ABCDEFG
    2A9Nodamor@gmail09/03/2019 07:01 AME9F9G9
    3A10Nodamor@hotmail09/03/2019 08:01 AME10F10G10
    4A11B11C1109/03/2019 09:01 AME11F11G11
    5A12Nodamor@yahoo09/03/2019 10:01 AME12F12G12
    6A13B13C1309/03/2019 11:01 AME13F13G13
    7A14B14C1409/03/2019 12:01 PME14F14G14
    8A15B15C1509/03/2019 01:01 PME15F15G15


    Must be excel dates. Or how do you have the data?
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

    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

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

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

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,236
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

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

  8. #8
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

    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

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

    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

  10. #10
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Delete rows if cell value is greater than a specific time

    Thanks for letting us know and you're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •