[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help With Excel Advanced Filter code

Posted on 2014-03-25
8
Medium Priority
?
693 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 53

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

649 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