Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel AutoFilter (2000)

    Hello. I have a spreadsheet where there is one column with more than 20 unique expressions or values. I want to filter out 5 of these 20 values so only rows with the remaining 15 values remain. I keep getting an error when I use the AutoFilter function. How can I filter out more than just 2 since the custom drop down only allows for 2 values to be filtered out.

    Sub FilterDefect()
    '
    ' FilterDefect Macro
    ' Macro recorded 9/7/2006 by BearingPoint
    '

    '
    Range("A1:N94").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=3, _
    Criteria1:="<>Cancelled - Duplicate", _
    Operator:=xlAnd, Criteria2:="<>Closed", _
    Operator:=xlAnd, Criteria3:="<>Duplicate - Identified", _
    Operator:=xlAnd, Criteria4:="<>Rejected - Not Reproducible", _
    Operator:=xlAnd, Criteria5:="<>Rejected - Missing Information"
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel AutoFilter (2000)

    If you look up the AutoFilter method in the Excel VBA help, you'll see that it doesn't have arguments Criteria3 etc., and you can specify the Operator argument only once. If you want complex criteria, you should create a Criteria range and use Advanced Filter.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel AutoFilter (2000)

    You could add the list of items you want to filter ot a range like

    <table border=1><td></td><td align=center>Q</td><td align=center valign=bottom>1</td><td valign=bottom>Cancelled - Duplicate</td><td align=center valign=bottom>2</td><td valign=bottom>Closed</td><td align=center valign=bottom>3</td><td valign=bottom>Duplicate - Identified</td><td align=center valign=bottom>4</td><td valign=bottom>Rejected - Not Reproducible</td><td align=center valign=bottom>5</td><td valign=bottom>Rejected - Missing Information</td></table>

    and then in a blank column (starting in O2, for example) enter:
    =ISERROR(MATCH(C2,$Q$1:$Q$5,0))
    and copy it from O3:O94.

    It will be TRUE if not in the list (to display) and FALSE if in the list. You can Filter on column O directly with True to display the items other than the 5 in Q1:Q5

    Steve

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoFilter (2000)

    My question is about the autofilter and what i need it for.

    In my workbook autofilter is use to hide row in (column "A") that are 0 values which i have turned off in tools > options so 0's are actually blanks. I activate non blanks for printing only the needed
    information on my worksheet.

    I run it to problems though when I use my save macro which creates a data file. because it moves a range of cells I am having trouble with moving all the data to the datafile, only the data that is not hidden by the autofilter moves. I do know if there is a way to get all the data instead of just the filtered data. I know that the datafile is properly loaded when autofilter is set to all so its the autofilter that has a charteristic that is troublsome. So I wanted to figure out how to get around it.

    I am trying to write a piece of code that says, if autofilter is not set to "all" then msgbox "Price Sheet autofilter is set please expand "All" feature and retry save" exit sub
    if the autofiler is set to all then continue into the code.

    I am not sure how to do this.

    If there is a way to change my data sheet with the autofilter, so that I get all data even when autofilter is on that would be much better

    Thanks so much.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel AutoFilter (2000)

    On error resume Next
    If Sheets("Price").FilterMode Then
    Sheets("Price").ShowAllData
    End If
    On error goto 0

    If you add this to the save code it will make the autofilter show all without bugging the user with a message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •