darls15
asked on
Help With Excel Advanced Filter code
Hi Experts
I have attached an example document with an advanced filter module (CriteriaFilter). The module works quite well apart from when no criteria has been specified (on the FILTER tab).
When I select a blank option in the dropdown lists (FILTER!C2:C7), I would like the module to ignore this filter criteria in the criteria range (RawData!M2:R3) and filter any records in the raw dataset (RawData!B2:K125) for this column whether it is blank or non-blank.
I have tried using formula to rectify this, e.g. =IF(FILTER!C4="","",FILTER !C4), however this doesn't seem to make any difference. For example, if I use the Start date of 01/01/2013, End date of 10/01/2014 and choose the blank option for cells FILTER!C2:C7, I believe the result should be 94 records, however only 65 are returned in the results (FILTER!E2:N2).
Any assistance would be appreciated.
Thanks
darls15
AdvFilterExample.xlsm
I have attached an example document with an advanced filter module (CriteriaFilter). The module works quite well apart from when no criteria has been specified (on the FILTER tab).
When I select a blank option in the dropdown lists (FILTER!C2:C7), I would like the module to ignore this filter criteria in the criteria range (RawData!M2:R3) and filter any records in the raw dataset (RawData!B2:K125) for this column whether it is blank or non-blank.
I have tried using formula to rectify this, e.g. =IF(FILTER!C4="","",FILTER
Any assistance would be appreciated.
Thanks
darls15
AdvFilterExample.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi MacroShadow
Thank you for responding. I copied and tested your code and when I cleared the filters from the Region, Audience, Session type and Presenter fields and set the Start date to 01/01/2013 and End date to 10/01/2014, I still get 65 records.
Thanks
darls15
Thank you for responding. I copied and tested your code and when I cleared the filters from the Region, Audience, Session type and Presenter fields and set the Start date to 01/01/2013 and End date to 10/01/2014, I still get 65 records.
Thanks
darls15
ASKER
Hi Rgonzo1971
I've tested the code you provided and it works really well, thank you very much for helping with this.
Thanks
darls15
I've tested the code you provided and it works really well, thank you very much for helping with this.
Thanks
darls15
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for darls15's comment #a39957667
for the following reason:
Perfect, thank you!
Accepted answer: 0 points for darls15's comment #a39957667
for the following reason:
Perfect, thank you!
Hi,
According to the Author's comment my code worked for hin7her very well, pls give points accordingly
Regards
According to the Author's comment my code worked for hin7her very well, pls give points accordingly
Regards
ASKER
Sorry Rgonzo1971 not sure how 0 points awarded, hopefully it takes this time.
Open in new window