Solved

Help With Excel Advanced Filter code

Posted on 2014-03-25
8
666 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 43
FormulaArray VBA Issue 6 17
Excel callender with date slider 5 27
nested if statement in excel help 4 15
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

895 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

13 Experts available now in Live!

Get 1:1 Help Now