Multiple or nested AdvancedFilters?
 

These successive VBA statements don't return "cumulative" results.

Range("G2:G2000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("G2499:G2510")

Range("K2:K2000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("K2499:K2510")

In other words, filter 2 doesn't apply to the results of filter 1.


So I tried to count how many rows are left after filter 1, and adjust the
range for filter 2

Range("G2:G2000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("G2499:G2510")

countFilteredRows = SUBTOTAL(3,A3:A2000)
Range("K2:K" & countFilteredRows).AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("K2499:K2510")


That doesn't work either.

Any ideas how I can make this work?


Thanks

Multiple or nested AdvancedFilters? (RESOLVED)
 

SOLUTION:

Use Excel like you're supposed to:

1) build the data in the CriteriaRange correctly
2) apply one AdvancedFilter to the whole shebang

Voila!




DFS wrote:
These successive VBA statements don't return "cumulative" results.

Range("G2:G2000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("G2499:G2510")

Range("K2:K2000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("K2499:K2510")

In other words, filter 2 doesn't apply to the results of filter 1.


So I tried to count how many rows are left after filter 1, and adjust
the range for filter 2

Range("G2:G2000").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("G2499:G2510")

countFilteredRows = SUBTOTAL(3,A3:A2000)
Range("K2:K" & countFilteredRows).AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:=Range("K2499:K2510")


That doesn't work either.

Any ideas how I can make this work?


Thanks