delete previous rows on date and time

[Mr Marvin]

i all, i have this macro below which deletes all rows from the prev day.

Sub DeleteFromDate()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim LR As Long
LR = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row
DateR = Application.InputBox("Enter based on date to delete", TitleMsg, FormatDateTime(Date, vbShortDate), Type:=1)
Cells.AutoFilter Field:=6, Criteria1:="<=" & DateR
ALR = ActiveSheet.Range("F" & Rows.Count).End(xlUp).Row
If ALR > 1 Then
Range("F2:F" & LR).SpecialCells(xlCellTypeVisible).Select
Range("F2:F" & LR).Delete
Range("F1").Activate
End If
Cells.AutoFilter
MsgBox "Finished deleting rows"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

on my report i have it sorted on the created date and the column which details the created date and time is shown as e.g. Oct 4, 2021 7.55 PM. with the macro above it removes all rows from the previous day but what i am trying to is expand that and remove any rows that have the created date before 8.00 PM the previous day. So my daily report which i received on the 5th would show all information from the 4th backwards. but i only need the rows from the 3rd, 8.00 PM onwards.


Any help or guidance if this is possible would be greatly appreciated

thanks

 

[Alex Blakenburg]

Can you give me an XL2BB of the 1st 10 rows of your data sheet ? or else a copy of your workbook via Dropbox, Google Drive, Onedrive or any sharing platform ?

Is your date column still F ?
In what row and column does your data start ?

 

[Mr Marvin]

hi alek

hi have the header starting in A1 but actual data starts B1 and col F is the created date

Agreement	Agr Serial	Created Date	Event Date
21449504	7302404	Oct 10, 2021 6:12 PM	Oct 10, 2021 6:46 PM
21449503	7302403	Oct 10, 2021 6:09 PM	Oct 10, 2021 6:11 PM
21449475	7302373	Oct 10, 2021 5:14 PM	Oct 10, 2021 6:04 PM
21449474	7302372	Oct 10, 2021 5:14 PM	Oct 10, 2021 5:16 PM
21449451	7302349	Oct 10, 2021 4:52 PM	Oct 10, 2021 4:59 PM
21449449	7302347	Oct 10, 2021 4:51 PM	Oct 10, 2021 5:02 PM
21449445	7302343	Oct 10, 2021 4:47 PM	Oct 10, 2021 4:50 PM
21449442	7302340	Oct 10, 2021 4:46 PM	Oct 10, 2021 4:52 PM
21449441	7302339	Oct 10, 2021 4:46 PM	Oct 10, 2021 6:59 PM

 

[Alex Blakenburg]

I really need to see context. Based on created date being F you are only showing me D:G.
I need to see how A:C are populated as well.

Also the copied in date columns is coming across as text.
Can you confirm that if you change the format of column F to say your standard short date format does the format actually change. If not then it is text at your end, if it does then the copy paste converted it to text and I will need to change it back to a date at my end for testing.

 

[Alex Blakenburg]

The only way I have been able to reproduce your error is for column 6 to not be within the filter range of columns.

 

[Mr Marvin]

Can you give me an XL2BB of the 1st 10 rows of your data sheet ? or else a copy of your workbook via Dropbox, Google Drive, Onedrive or any sharing platform ?

Is your date column still F ?
In what row and column does your data start ?

this is what it would it be like after, apologies i cant get it on any either way,

 

[Alex Blakenburg]

I can't get it to fail.
Do you have the code in a standard module ("not" in sheet module) ?
Also how are you running the code ?
And what is the sheet name the data is on ?

Can you use the VBA button and send me back the code you are actually using ?

 

[Mr Marvin]

hi alek, i have managed to get this working now. many thanks for your assistance on this.

 

[Alex Blakenburg]

No problem. Glad I could help.
I would be interested to know what you needed to do to get it working though, if you get a chance.

 

[Mr Marvin]

i was testing the macro on a test sheet. when i used it on the main sheets it worked perfectly.

 

[Alex Blakenburg]

I appreciate you coming back and closing the loop for me. Thank you.