PDA

View Full Version : [SOLVED] AutoFilter Previous Months Data



LutonBarry
12-03-2015, 09:24 AM
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]

Charlize
12-05-2015, 07:02 AM
You could try this one.

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

LutonBarry
12-05-2015, 01:45 PM
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

Stickers
03-06-2021, 08:05 PM
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.

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
03-08-2021, 12:40 PM
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.

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

Stickers
03-08-2021, 03:40 PM
Thank you very much Charlize