Solved

Help With Excel Advanced Filter code

Posted on 2014-03-25
8
644 Views
Last Modified: 2014-03-27
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
0
Comment
Question by:darls15
  • 4
  • 2
8 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
I would suggest refrain from using the selection object when not necessary, anyways, here is the code you're after:
Sub FilterData()
    Sheets("Filter").Select
    Columns("E:XFD").Select
    Selection.Clear
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("E2").Select
    Sheets("RawData").sort.SortFields.Clear
    Sheets("RawData").Range("B2:K125").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("RawData").Range("M2:R3"), CopyToRange:=Sheets("Filter").Range("E2:N2"), Unique:=True
    Columns.AutoFit
    Range("E2").Select
End Sub 

Open in new window

0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

In Raw Data I put formulas to rearrange the filters in accordance to how many they are

and then used

Sub FilterData()
    Dim FilterRange As Range
    Set RDsh = Sheets("RawData")
    NrOfFilters = 6 - Evaluate("=COUNTIF(RawData!T3:Y3,"""")")
    With RDsh
        Set FilterRange = .Range(.Cells(2, 20), .Cells(3, 19 + NrOfFilters))
    End With

    Sheets("Filter").Select
    Columns("E:XFD").Select
    Selection.Clear
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("E2").Select
    Sheets("RawData").sort.SortFields.Clear
    
    Sheets("RawData").Range("B2:K125").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        FilterRange, CopyToRange:=Sheets("Filter").Range("E2:N2"), Unique:=True
    Columns.AutoFit
    Range("E2").Select
End Sub

Open in new window

You won't get 94 because there is a duplicate

Regards
AdvFilterExampleV1.xlsm
0
 

Author Comment

by:darls15
Comment Utility
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:darls15
Comment Utility
Hi Rgonzo1971

I've tested the code you provided and it works really well, thank you very much for helping with this.

Thanks
darls15
0
 

Author Comment

by:darls15
Comment Utility
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!
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

According to the Author's comment my code worked for hin7her very well, pls give points accordingly

Regards
0
 

Author Closing Comment

by:darls15
Comment Utility
Sorry Rgonzo1971 not sure how 0 points awarded, hopefully it takes this time.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now