Multiple Filter Criteria in Excel VBA

[D Zandveld]

Hi, I have a challenge

I have an array of data in a worksheet called "Imported Data", Some of these
cells contain no data.

I have a series of drop-down lists in a worksheet called "Main", in the
cells D11, D13, D15, D17, D19, D21, D23, D25, D27, D33, D35, D37.

What I would like is for the user to select 1 or more values from the lists
in "Main", and filter the "Imported Data" based on these criteria. At the
moment I have this which seems to filter EVERYTHING out, not just the
selected criteria.

The results then need to be shown in a worksheet called "Report"

***************Current Filtering Code as follows:********************
Sheets("Imported Data").Activate
'****Code to copy data to "Report" worksheet omitted****
Sheets("Report").Activate
Selection.AutoFilter Field:=3, Criteria1:=Worksheets("Main").Range("D11"),
Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:=Worksheets("Main").Range("D13"),
Operator:=xlAnd
Selection.AutoFilter Field:=6, Criteria1:=Worksheets("Main").Range("D15"),
Operator:=xlAnd
Selection.AutoFilter Field:=7, Criteria1:=Worksheets("Main").Range("D17"),
Operator:=xlAnd
Selection.AutoFilter Field:=10, Criteria1:=Worksheets("Main").Range("D19"),
Operator:=xlAnd
Selection.AutoFilter Field:=11, Criteria1:=Worksheets("Main").Range("D21"),
Operator:=xlAnd
Selection.AutoFilter Field:=13, Criteria1:=Worksheets("Main").Range("D23"),
Operator:=xlAnd
Selection.AutoFilter Field:=12, Criteria1:=Worksheets("Main").Range("D25"),
Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:=Worksheets("Main").Range("D27"),
Operator:=xlAnd
Selection.AutoFilter Field:=19, Criteria1:=Worksheets("Main").Range("D33"),
Operator:=xlAnd
Selection.AutoFilter Field:=20, Criteria1:=Worksheets("Main").Range("D35"),
Operator:=xlAnd
Selection.AutoFilter Field:=21, Criteria1:=Worksheets("Main").Range("D37")

 

[OssieMac]

Bit difficult with info given but I am suspecting that you are setting all of
the filters irrespective of whether the user actually makes a selection in
every case and your code is possibly using the default values or blanks or
nulls or something. You possibly need to identify which filters the user
requires and the ones not required you should code to select all for that
column.

 

[Jay]

If OssieMac is correct, and it's multiple blank criteria that are leaving you
with an empty list, try preceeding each "Selection.Autofilter...." statement
with:

If Range("D1") Then Selection.Autofilter....etc.
(change "D1" to match the address in the autofilter statement)

The other possibility is that twelve simultaneous autofilter criteria might
not be fulfilled in your database.

 

[D Zandveld]

I actually tried something else:

If you put an * as the default character in the drop-down (eg. not a blank),
what I had worked perfectly and efficiently.

Thanks for your response, hope someone else can learn from this.

Thanks

 

[Jay]

Clever !
--
Thanks for the discovery,
Jay

I actually tried something else:

If you put an * as the default character in the drop-down (eg. not a blank),
what I had worked perfectly and efficiently.

Thanks for your response, hope someone else can learn from this.

Thanks