VBA to delete rows older than 30days (No Loop)

[Almach]

Hello,

In VBA I am looking to delete entire row rows based on the age of the date in column AI. I am currently deleting rows older than 31 days, but since it deletes one row at a time it takes a long times since there are thousands of rows to delete each time I update it. I am not the greatest with VBA. Just a basic understanding and most of the codes I write are just Frankensteined together, so having a hard time figuring this out myself.

Instead of looping through the code line by line, I'm thinking it might be possible to set my range dynamically, but not sure how to go about doing that. My data is always organized from oldest to newest so I dont need to search the whole data set for rows to delete. I would just need to delete from row 2 to the last row that is older than 31 days.


This is the delete code I am currently using in my macro, but its too slow to be viable. (I just call it the main VBA so I can easily turn it on and off when running the main code)

Sub Delete()

    Application.ScreenUpdating = False
    Dim i As Long
    Dim LastRow As Long

    With Sheets("Data")
        LastRow = .Cells(.rows.Count, "AI").End(xlUp).Row

        For i = LastRow To 2 Step -1
            If DateDiff("d", .Range("AI" & i).Value, Date) > 31 Then
                .rows(i).Delete
            End If
        Next i
    End With
    Application.ScreenUpdating = True

End Sub

Thanks for any advice or help!

 

[Michael M]

Maybe this way....UNTESTED

Sub MM1()
Dim lr As Long
lr = Sheets("Data").Cells(Rows.Count, "AI").End(xlUp).Row
With Sheets("Data").Range("AI2:AI" & lr)
    .AutoFilter Field:=1, Criteria1:=">" & 31, Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

 

[Almach]

Thank you for the response Michael.

I tried running the VBA and it fails at the AutoFilter line.
Run-time error '1004':
AutoFilter method of Range class failed

I tried manually filtering the data just to see what it does and it doesn't seem to filter the data correctly at all. Even if I tell it to do today only it shows a bunch of older dates. The column is in Date/Time format so I don't know if that's what's causing it to act strange.

I do have a column AI that is date only, but its in text format. If you think it would be better to convert that to a date column and used that. I can do that during the import process for new days.

Thank you again for the help!

 

[Michael M]

can you post a smaple of your data ??

 

[Almach]

Sorry for the delay, yesterday ended up being a busy day. I have removed all data but the two date columns. Essentially trying to just do a 30 day rolling, so as it pastes a new day, it deletes the old days. Column B I did convert to a date since it looks like the load times from the data base aren't always once a day or everyday. Tried running the VBA off that column instead and i got the same error.

30 Day Rolling.xlsm
	A	B
1	load_datetime	Report_Date
2	1/25/22 16:11	1/24/2022
3	1/26/22 16:10	1/25/2022
4	1/27/22 16:12	1/26/2022
5	1/29/22 16:09	1/28/2022
6	1/30/22 16:09	1/29/2022
7	1/31/22 16:10	1/30/2022
8	2/1/22 16:14	1/31/2022
9	2/2/22 17:00	2/1/2022
10	2/3/22 16:13	2/2/2022
11	2/4/22 16:14	2/3/2022
12	2/5/22 16:11	2/4/2022
13	2/6/22 16:12	2/5/2022
14	2/7/22 16:12	2/6/2022
15	2/8/22 16:25	2/7/2022
16	2/9/22 16:14	2/8/2022
17	2/10/22 16:21	2/9/2022
18	2/11/22 16:16	2/10/2022
19	2/12/22 16:24	2/11/2022
20	2/13/22 16:15	2/12/2022
21	2/14/22 16:17	2/13/2022
22	2/15/22 16:27	2/14/2022
23	2/17/22 14:41	2/15/2022
24	2/17/22 16:50	2/16/2022
25	2/18/22 16:17	2/17/2022
26	2/19/22 16:16	2/18/2022
27	2/20/22 16:16	2/19/2022
28	2/21/22 16:38	2/20/2022
29	2/22/22 16:34	2/21/2022
30	2/23/22 16:18	2/22/2022
31	2/24/22 16:34	2/23/2022
32	2/25/22 16:20	2/24/2022
33	2/26/22 16:16	2/25/2022
34	2/27/22 16:17	2/26/2022
35	2/28/22 16:31	2/27/2022
36	3/2/22 7:04	2/28/2022
37	3/2/22 18:24	3/1/2022
Data

 

[Almach]

Mike thanks for your help. I actually found another thread with a similar issue to mine. I don't know why I couldn't find it two days ago, but I guess the way I worded the search today worked better. Appreciate you taking time and helping me solve my issue!

VBA/MACRO to delete rows based on date

I'd like to create a macro to delete rows based on a date (let's say all dates prior to 1/1/2016) from a file. The date field is column B. Could someone please help put together the code? Below is a sample data set Col1 Col2 Col3 Col4 1 2-Jan-14 ABC 782350 2 2-Jan-14 ABC...

www.mrexcel.com

I ended up modifying and using this code and just modifying it for my needs.

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

 

[Michael M]

Ok, glad you got the rquired result...