Applying 2 filters via a Macro

[pete212]

Hello experts,

Please consider the following siutation:

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

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

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

This works as long as there is row data for each contract number durin
those periods. If there isn't a row with a certain contract numbe
during the dates applied by the first filter, the macro falls over as i
then can't apply the second filter to data that doesnt exist. Hopefull
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
contract numbers in it. If, say, 3228260 is not present between 1st Fe
and 1st April, then "3228260" is not an available option on the filter
therefore the macro can't 'select' it, therefore the macro falls ove
and throws an error.

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

Many thanks for any help you can give

 

[Claus Busch]

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

 

[Claus Busch]

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

 

[pete212]

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

Claus - Your an Excel Genius!

Thanks very much