Advanced filter on last months results

[eidde1]

Hi
Is it possible with VBA to filter last months data. The date column of my data is Column D. So basically if I run a Macro in December 2013 I only want to end up with November 2103 results, jan 2014 would give me Dec 2013 etc.

Many thanks for any help

Eddie

 

[Claus Busch]

Hi Eddie,

Am Fri, 27 Dec 2013 09:42:02 -0800 (PST) schrieb (e-mail address removed):

Is it possible with VBA to filter last months data. The date column of my data is Column D. So basically if I run a Macro in December 2013 I only want to end up with November 2103 results, jan 2014 would give me Dec 2013 etc.

try:
With ActiveSheet.Range("D")
.AutoFilter Field:=1, Criteria1:= _
xlFilterLastMonth, Operator:=xlFilterDynamic
End With


Regards
Claus B.

 

[eidde1]

Hi Eddie,



Am Fri, 27 Dec 2013 09:42:02 -0800 (PST) schrieb (e-mail address removed):






try:

With ActiveSheet.Range("D")

.AutoFilter Field:=1, Criteria1:= _

xlFilterLastMonth, Operator:=xlFilterDynamic

End With





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi Claus
Thanks for you suggestion I am using Excel 2003 and It look like xlFilterLastMonth will not work with this version?

Thanks anyway
Eddie

 

[Claus Busch]

Hi Eddie,

Am Sat, 28 Dec 2013 04:58:22 -0800 (PST) schrieb (e-mail address removed):

Thanks for you suggestion I am using Excel 2003 and It look like xlFilterLastMonth will not work with this version?

then try:
Sub Test()
Dim StartD As Double
Dim EndD As Double

StartD = DateSerial(Year(Date), Month(Date) - 1, 1)
EndD = DateSerial(Year(Date), Month(Date), 0)

ActiveSheet.Range("D").AutoFilter Field:=1, Criteria1:= _
">=" & StartD, Operator:=xlAnd, Criteria2:="<=" & EndD
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sat, 28 Dec 2013 14:08:58 +0100 schrieb Claus Busch:

then try:

or try:

Sub Test()
Dim StartD As Double
Dim EndD As Double

EndD = WorksheetFunction.EoMonth(Date, -1)
StartD = DateSerial(Year(EndD), Month(EndD), 1)

ActiveSheet.Range("D").AutoFilter Field:=1, Criteria1:= _
">=" & StartD, Operator:=xlAnd, Criteria2:="<=" & EndD
End Sub


Regards
Claus B.

 

[eidde1]

Hi Eddie,



Am Sat, 28 Dec 2013 04:58:22 -0800 (PST) schrieb (e-mail address removed):






then try:

Sub Test()

Dim StartD As Double

Dim EndD As Double



StartD = DateSerial(Year(Date), Month(Date) - 1, 1)

EndD = DateSerial(Year(Date), Month(Date), 0)



ActiveSheet.Range("D").AutoFilter Field:=1, Criteria1:= _

">=" & StartD, Operator:=xlAnd, Criteria2:="<=" & EndD

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks Claus

This did exactly what i need - your second suggention threw up a debug error on

EndD = WorksheetFunction.EoMonth(Date, -1)

But I will go with the VBA above.

Thanks once again for your wonderful help as usual

Eddie

 

[Claus Busch]

Hi Eddie,

Am Sat, 28 Dec 2013 16:47:06 -0800 (PST) schrieb (e-mail address removed):

This did exactly what i need - your second suggention threw up a debug error on

EndD = WorksheetFunction.EoMonth(Date, -1)

both suggestions worked fine. I always test my suggestions before
posting.

Regards
Claus B.