Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Auto-filtering with multiple criteria (Excel 2003)

    Good morning.....I have a wsheet with several columns (A-L) of data; I want to autofilter data based on criteria in columns F & K.....[column F is the 6th column; the criteria I am using is whether there is anything in the cells in column F)....at this point, I am using the following to filter based on whether there is any data in column F:

    Sub Print_schedule()
    '
    ' Print_schedule Macro
    ' Macro recorded 3/14/2007 by David J. McNab
    '

    '
    Application.Run "Scheduler 2008.XLS'!AF_courtrooms"
    ActiveSheet.Unprotect Password:="secret"
    Selection.AutoFilter Field:=6, Criteria1:="*"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowSorting:=True, AllowFiltering:=True, Password:="open"
    End Sub

    I wish to add a line such as "Selection.AutoFilter Field:=11, Criteria1:="*".....hoping that this will make the code first filter field 6 (col F) based on whether there is data in column F, and then, filter field 11 (col K) based on the same.....I have tried using ELSE, or THEN, or AND followed by "Selection.AutoFilter Field:=11, Criteria1:="*" but I get an error.....is there a way to perform this filtering of 2 non-adjacent columns ...? Thanks for any help or suggestions/

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

    Re: Auto-filtering with multiple criteria (Excel 2003)

    You simply add another AutoFilter line below the first one:

    Selection.AutoFilter Field:=11, Criteria1:="*"

    The filter will be added to the already existing one.

    BTW shouldn't you use <> instead of *?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Auto-filtering with multiple criteria (Excel 2

    Hi Hans.....I tried your suggestion before posting and it returns an error....Run Time Error 1004.....for example, in my wsheet I have data in F10 and other data in K12.....or, F15, F30 and K20, K22 etc etc....I am trying to 'filter' the rows in F & K that have data in them, so that in this example, it would filter F10, F15, K12, K20, K22, F30..........and hide all other rows b/c there is no data in them......maybe I am asking the 'filter' to do contradictory things (eg: filter out F12 b/c it has no data, but don't filter it b/c K12 has data)....that's why I was wondering if I had to use an AND or an ELSE..??

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

    Re: Auto-filtering with multiple criteria (Excel 2

    AutoFilter always combines the conditions on different columns with AND: display the rows that meet the criteria for both column F AND column K. In your example, rows that contain a non-blank value in column F and a non-blank value in column K.

    Apparently you want to display rows that contain a non-blank value in column F OR in column K (or both). AutoFilter doesn't provide that option. You'll have to use Advanced Filter for that. with a criteria range. You can specify OR conditions by placing them in different rows. See the screenshot below ("Field F" and "Field K" should be replaced with the column headers/field names for column F and K)
    Attached Images Attached Images
    • File Type: png x.png (524 Bytes, 8 views)

  5. #5
    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: Auto-filtering with multiple criteria (Excel 2

    A simple way is to use a neighboring blank column and enter something like =CountA(F2,K2) and copy it down the column. Then you can filter on this column for <>0 to display those data in one of the columns

    Steve

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Auto-filtering with multiple criteria (Excel 2

    Hi Hans and Steve....thank you both for the suggestions on this topic....I couldn't quite get it to work the way you guys suggested, but your posts did cause me to re-think the problem and I was able to develop a variation on what you guys suggested, so it worked out nicely in the end....thanks again for your help (and your teaching!!).

Posting Permissions

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