Advanced Filter Multiple Criteria Range not working

[dean.brunne]

Hi,

I have used the following Advanced Filter using named ranges for the
List, Criteria and CopyToRange. The code runs but only half of the
criteria seems to work. I try the same Advanced filter manually with
the same result. What do I need to have multiple criteria work. The
criteria below works only for the Cost Centre field but not the
Company field. The Company field returns all company numbers but I
want only the one. Appreciate any help. Cheers, Dean.

CODE:

Sheets("Sheet1").Select
Range("A1").Select
Sheets("Detail").Range("Database").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Distribution").Range("H1:I17"),
CopyToRange:=Range( _
"A1:I1"), Unique:=False

CRITERIA

Company Cost Centre
2100 4000
4010
4021
4022
4023
4025
4026

 

[dean.brunne]

Hi,

I have used the following Advanced Filter using named ranges for the
List, Criteria and CopyToRange. The code runs but only half of the
criteria seems to work. I try the same Advanced filter manually with
the same result. What do I need to have multiple criteria work. The
criteria below works only for the Cost Centre field but not the
Company field. The Company field returns all company numbers but I
want only the one. Appreciate any help. Cheers, Dean.

CODE:

Sheets("Sheet1").Select
Range("A1").Select
Sheets("Detail").Range("Database").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Distribution").Range("H1:I17"),
CopyToRange:=Range( _
"A1:I1"), Unique:=False

CRITERIA

Company Cost Centre
2100 4000
4010
4021
4022
4023
4025
4026

Hi I have now got it to work by putting 2100 with each cost centre
however I have other filters to do where the Company list is 4 records
and the Cost Centre list is 12. Do I therefore need to create 48
combos in the criteria list or can the criteria statement be
structured differently.

NEW CRITERIA EXAMPLE:
Company Cost Centre
2100 7000
2200 7020
2300 7035
2400 7040
7045
7055
7065
7070
7075
7080
7115
7120

 

[Debra Dalgleish]

Create a criteria range (H1:H2) with a blank heading cell, and in the
cell below, use a formula that refers to your company list and cost
centre list. For example:

=AND(COUNTIF(Companies,F2),COUNTIF(CostCtrs,J2))

where the first company in the data is in cell F2, and the first cost
centre is in J2.