Applying 2 filters via a Macro
 

Hello experts,

Please consider the following siutation:

In column A I have contract numbers which can be one of 4 different numbers.

In column C I have dates, formated as mmm-yy.

I have a Macro that filters column C for a certain period - this works great. What I then what it do is filter OUT one contract number, so that only data is shown for 3 out of the 4 contract numbers for the period filtered.

This works as long as there is row data for each contract number during those periods. If there isn't a row with a certain contract number during the dates applied by the first filter, the macro falls over as it then can't apply the second filter to data that doesnt exist. Hopefully the code below will help explain this.

Range("C1").Select
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=-2
ActiveSheet.Range("$A$1:$Z$53").AutoFilter Field:=3, Operator:= _
xlFilterValues, Criteria2:=Array(1, "2/1/2012", 1, "3/1/2012", 1, "4/1/2012")

This bit works great. Then:

Range("A1").Select
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=-2
ActiveSheet.Range("$A$1:$Z$53").AutoFilter Field:=1, Criteria1:=Array( _
"3228260", "3228276", "3554229"), Operator:=xlFilterValues

This works providing there is 1 or more rows with each of those 3 contract numbers in it. If, say, 3228260 is not present between 1st Feb and 1st April, then "3228260" is not an available option on the filter, therefore the macro can't 'select' it, therefore the macro falls over and throws an error.

Does that make sense? Does anyone know any way around this?

Many thanks for any help you can give.

Applying 2 filters via a Macro
 

Hi Pete,

Am Tue, 15 May 2012 16:15:53 +0000 schrieb pete212:

I have a Macro that filters column C for a certain period - this works
great. What I then what it do is filter OUT one contract number, so that
only data is shown for 3 out of the 4 contract numbers for the period
filtered.

This works as long as there is row data for each contract number during
those periods. If there isn't a row with a certain contract number
during the dates applied by the first filter, the macro falls over as it
then can't apply the second filter to data that doesnt exist. Hopefully
the code below will help explain this.

Try:
With ActiveSheet.Range("A1")
.AutoFilter Field:=1, Criteria1:=Array("3228260", "3228276", "3554229")
.AutoFilter Field:=3, Criteria2:= _
Array(1, "2/1/2011", 1, "3/1/2011", 1, "4/1/2011")
End With


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Applying 2 filters via a Macro
 

Hi Pete,

the copy of the code is not correct and the year is 2011 instead of
2012:

With ActiveSheet.Range("A1")
.AutoFilter Field:=1, Criteria1:=Array("3228260", "3228276",
"3554229"), _
Operator:=xlFilterValues
.AutoFilter Field:=3, Operator:=xlFilterValues, Criteria2:= _
Array(1, "2/1/2012", 1, "3/1/2012", 1, "4/1/2012")
End With


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus - Your an Excel Genius!

Thanks very much!