Solved

Help With Excel Advanced Filter code

Posted on 2014-03-25
8
679 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 50

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 50

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
NEED TRANSFER  DATA 59 24
VBA If statement with filtered values 4 18
Slow calculation 2 21
Macro show matches 3 33
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

820 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