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

 

[Alex Blakenburg]

Please try the macro on the workbook you shared just to the confirm that the issue still exists in the data you sent us.
PS: And the Actual Tab Name is Sheet1 right ? not the code name or position of the sheet.

 

[Alex Blakenburg]

One other question, do you have the code in the same workbook as the data ?

 

[SCPbrito]

Yes the tab sheet is called "Sheet1". The macro is saved to my personal workbook.
I tried the data set provided with the code earlier today but didnt work. I will try again tomorrow with video to try to pinpoint the issue

 

[Alex Blakenburg]

The macro is saved to my personal workbook.

That will be your issue.
The code contains this line, which in your case means it is looking at your personal workbook.

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

I consolidated the references to the workbook/worksheet in this:

Sub MyMacro_JoePost17_modified()

    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 = ActiveWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
  
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add2 Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    'With ws.Sort
    With ws.Sort
        .SetRange ws.Range("$A$1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Columns("B:B").NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"   ' XXX Not sure you need this
    ' 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)
    Application.ScreenUpdating = False
    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
    Application.ScreenUpdating = True
  
    MsgBox "Macro complete!"

End Sub

 

[Joe4]

The macro is saved to my personal workbook.

Ah, there is the critical bit of information that was not mentioned before!

As Alex pointed out, "ThisWorkbook" will always refer to the workbook that the Macro is stored in, so your code will never run successfully against any other file than the one the code is stored in.
You need to use "ActiveWorkbook" instead, to run it against whatever workbook is active at the time you run the procedure.

 

[SCPbrito]

Thanks for clarifying. I just started with excel at my new job so this is all foreign to me. The macro worked as intended. Thanks soo much for all the help

 

[Joe4]

You are welcome. Glad we could help!

Yes, the devil is often in the details left out!