|
Multiple Filter Criteria in Excel VBA
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") |
Multiple Filter Criteria in Excel VBA
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. "D Zandveld" wrote: 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") |
Multiple Filter Criteria in Excel VBA
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. -- Jay "OssieMac" wrote: 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. "D Zandveld" wrote: 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") |
Multiple Filter Criteria in Excel VBA
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" wrote: 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. -- Jay "OssieMac" wrote: 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. "D Zandveld" wrote: 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") |
Multiple Filter Criteria in Excel VBA
Clever !
-- Thanks for the discovery, Jay "D Zandveld" wrote: 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" wrote: 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. -- Jay "OssieMac" wrote: 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. "D Zandveld" wrote: 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") |
| All times are GMT +1. The time now is 03:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com