AutoFilter Previous Months Data

I want to be able on a daily basis to filter out and delete data from the previous month.

So when January comes round for example it uses January 2016 as the current month and deletes everthing prior to that date.

I attach a sample spreadsheet and some code I've tried which literally filters out everything. The spreadsheet has a named range in cell "D1" called "Month".

[CODE]Sub Macro1()
'
' Macro1 Macro
'
'
Dim Month As Range
Range("D1").Select
Set Month = Range("D1").CurrentRegion
ActiveSheet.Range("$A$1:$B$2").AutoFilter Field:=2, Criteria1:= _
"<""Month"", Operator:=xlAnd"
End Sub[CODE]

You could try this one.

Code:

---

Sub only_active_month_visible_plus_future()
'startdate as date
Dim startDate As Date
'startdate as long for excel
Dim lstartDate As Long


'get start date, day = always 1
startDate = DateSerial(Year(Now), Month(Now), 1)
lstartDate = startDate
'filter everything before current month
'you see this month + future
ActiveSheet.Range("B1").AutoFilter Field:=2, Criteria1:=">=" & lstartDate
End Sub

---

Charlize

Charlize,

That looks good from where I am sitting and I've just tried it and it works a treat. I did find some code on line that looked like it may have worked but it was far more convoluted than your method.

So thankful to you.

Thanks

Hi @Charlize, can your code be modified to filter for two months prior including all the prior months ie in March filter for Jan and all months before, cheers.

Code:

---

Sub only_active_month_visible_plus_future()'startdate as date
Dim startDate As Date
'startdate as long for excel
Dim lstartDate As Long


'get start date, day = always 1
startDate = DateSerial(Year(Now), Month(Now), 1)
lstartDate = startDate
'filter everything before current month
'you see this month + future
ActiveSheet.Range("B1").AutoFilter Field:=2, Criteria1:=">=" & lstartDate End Sub

---

This code will filter the rows where the date is more than 2 months prior to the current month of the date.
In march, feb and jan is still shown. Older data is hidden. Play with numbers of month to be shown to test out.

Code:

---

Sub show_two_months_prior_to_current_date_and_future()
    'startdate as date for date minus two months
    Dim startDate As Date
    'startdate as long for excel
    Dim lstartDate As Long
   
    'I use the worksheetfunction edate to substract 2 months from current month
    'now = the current day/month/year and time so we only need to get the date part
    'by using dateserial.
    'edate takes care when the century becomes one less.
    'In Jan 2021 we need also Dec 2020 and Nov 2020 to be included in the filter
    'Change -2 to -3 or more to get dates prior of 2021.
    startDate = Application.WorksheetFunction.EDate(DateSerial(Year(Now), Month(Now), 1), -2)
    'startDate = DateSerial(Year(Now), Month(Now), 1)
    lstartDate = startDate
    'filter everything from two months prior to current date
    'you see rows starting from two months prior to current month of the date
    ActiveSheet.Range("B1").AutoFilter Field:=2, Criteria1:=">=" & lstartDate
End Sub

---

Charlize

Thank you very much Charlize