Delete rows before a certain time

[SCPbrito]

I have a data set that i need to sort entry numbers from Column"A" from smallest to largest. Then format column "B" to show date and time and then delete all rows that have a timestamp before 6:50am of the current day. Had some help from Chatgpt but it not deleting the rows.

 ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").Select
    Selection.NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"
    
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentTime As Date
    Dim cutoffTime As Date
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Find the last row with data in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Get the current date and time
    currentTime = Now
    ' Set the cutoff time for the current day at 6:50 AM
    cutoffTime = DateValue(currentTime) + TimeValue("06:50:00")

    ' Loop from the last row to the first row (bottom-up)
    For i = lastRow To 1 Step -1
        ' Check if the timestamp in column A is before the cutoff time
        If IsDate(ws.Cells(i, 1).Value) Then
            If ws.Cells(i, 1).Value < cutoffTime Then
                ' Delete the row if the condition is met
                ws.Rows(i).Delete
            End If
        End If
    Next i
End Sub

 

[iggydarsa]

last loop

For i = lastRow To 1 Step -1
        ' Check if the timestamp in column A is before the cutoff time
        If IsDate(ws.Cells(i, 1).Value) Then
            If ws.Cells(i, 1).Value < cutoffTime Then
                ' Delete the row if the condition is met
                ws.Rows(i).Delete
            End If
        End If
    Next i

looking at column A but looks like your date is on column B.

I would change Cells(i, 1) in this loop to Cells(i, 2) and see if it makes any difference

 

[SCPbrito]

Updated it and still no change. Rows are not deleted

 

[Joe4]

If you re-format column B on your sheet to show BOTH the date and time, what does it show as the entries?
Can you post that screen print here?

 

[Joe4]

I also see an error in the Sort section at the top. I don't know why you have the "Autofilter" before "Sort" in multiple lines of code.
If you are just trying to sort, the word "Autofilter" should not be there.

I tried this code and it all seemed to work correctly for me:

Sub MyMacro()

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"
    
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentTime As Date
    Dim cutoffTime As Date
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Find the last row with data in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Get the current date and time
    currentTime = Now
    ' Set the cutoff time for the current day at 6:50 AM
    cutoffTime = DateValue(currentTime) + TimeValue("06:50:00")

    ' Loop from the last row to the first row (bottom-up)
    For i = lastRow To 1 Step -1
        ' Check if the timestamp in column A is before the cutoff time
        If IsDate(ws.Cells(i, 2).Value) Then
            If ws.Cells(i, 2).Value < cutoffTime Then
                ' Delete the row if the condition is met
                ws.Rows(i).Delete
            End If
        End If
    Next i

End Sub

 

[SCPbrito]

I tried your code and rows were not being deleted. Here is the screencap you asked for.

 

[Alex Blakenburg]

I tested @Joe4's code and to make the sort work I had to add the line in blue:

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("$A$1").CurrentRegion

The delete worked fine for me and I have a different date format, so not sure why its not working for you.
Are you able to share your workbook via a sharing platform such as dropbox, google drive etc allowing access to anyone with the link and posting the link here ?

 

[Joe4]

It works just fine for me.

Are you getting errors, or is it just not doing anything?
If you are getting errors, what exactly do they say?

Does making Alex's adjustment help?

Are you sure your sheet is really named "Sheet1" and not something else?

 

[SCPbrito]

It Sorts Column A but does delete any rows. Let me see if i can provide a link to the file

 

[SCPbrito]

Here is link to the file. LMK if this helps

Loading Google Sheets