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

Hi Eddie,

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

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: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

On Friday, 27 December 2013 17:50:58 UTC, Claus Busch wrote:
Hi Eddie,



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



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: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

Hi Eddie,

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

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: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

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: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

On Saturday, 28 December 2013 13:08:58 UTC, Claus Busch wrote:
Hi Eddie,



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



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: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

Hi Eddie,

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2