VBA to filter only for current month

[IraAxa]

Hi there,

I need a vba that will filter cell only for its current month - i know the filter option, but i cannot use it every month since the month will change and want to make this more automated for someone who is not good with excel:

'Filtering Newly Created based on current month data

Sheets("SSS Rpt").Select
Range("$A$9:$KK$10000").AutoFilter Field:=80, Operator:= _
xlFilterValues, Criteria2:=Array(1, "6/30/2014")

so instead of 6/30/2014 - how do I set this filter for the current month only, because next month i'd have dates for July, and etc.

Also, is it possible to filter for a month before the current month, so onstead of filtering it for the current month, i want to filter it for the previous month?

 

[wigi]

Like this:

'Filtering Newly Created based on current month data
    Sheets("SSS Rpt").Range("A9:KK10000").AutoFilter Field:=80, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic

 

[IraAxa]

Perfect, this worked for the current month - would you happen to have for teh previous month or even month ahead?
thanks

 

[wigi]

Did you spend a couple of minutes in the helpfiles or using Google?
It's xlFilterLastMonth and xlFilterNextMonth.

 

[IraAxa]

no, but thanks a lot...

 

[fataemorganae]

Did you spend a couple of minutes in the helpfiles or using Google?
It's xlFilterLastMonth and xlFilterNextMonth.

Trying to filter using the xlFilterLastMonth but, i need on column 38 to filter "NOT" value and on column 42 to filter "Blanks" and Last Month but since I tried to include "LastMonth" i'm getting a sintax error.
Can you help?

 

[fataemorganae]

Trying to filter using the xlFilterLastMonth but, i need on column 38 to filter "NOT" value and on column 42 to filter "Blanks" and Last Month but since I tried to include "LastMonth" i'm getting a sintax error.
Can you help?

Anyone?

 

[jtgolden1s]

Hi, so i used the following code and it works perfect to sort by current month but is there a way to also get this code to sort for previous month? I have searched all over google. thanks!!

Sheets("SSS Rpt").Range("A9:KK10000").AutoFilter Field:=80, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic

 

[jtgolden1s]

Got it! XLFilterLastMonth

 

[josephmary]

Like this:

'Filtering Newly Created based on current month data
    Sheets("SSS Rpt").Range("A9:KK10000").AutoFilter Field:=80, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic

If your rows exceeds 10000 (worst case), i think it's better for the code to be:
'Filtering Newly Created based on current month data
Sheets("SSS Rpt").Range("A:KK").AutoFilter Field:=80, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic

So as to filter all current month even if your data exceeds the number of rows