Solved

Help With Excel Advanced Filter code

Posted on 2014-03-25
8
673 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 27

Expert Comment

by:MacroShadow
ID: 39956027
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 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39956348
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
ID: 39957663
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:darls15
ID: 39957667
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
ID: 39958155
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 49

Expert Comment

by:Rgonzo1971
ID: 39958156
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
ID: 39960372
Sorry Rgonzo1971 not sure how 0 points awarded, hopefully it takes this time.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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