+ Reply to Thread
Results 1 to 21 of 21

AutoFilter with multiple criteria in a single column

  1. #1
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    AutoFilter with multiple criteria in a single column

    Hi,

    Can anyone help me, I did try this code but when it go to the 2nd filter, it seems does not select anything.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: AutoFilter with multiple criteria in a single column

    * may only take two values at most with filter.

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,186

    Re: AutoFilter with multiple criteria in a single column

    Hi, Faridwahidi,

    please do not start multiple posts on the same subject [http://www.excelforum.com/excel-prog...haracter.html] but stay in the original thread or at least post a link to the other thread.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    Hi AB33,

    How to make it more than 2 criteria, I really need to use that method. Please help me.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: AutoFilter with multiple criteria in a single column

    Did you try it this way?

    Please Login or Register  to view this content.
    Last edited by xladept; 07-20-2014 at 12:04 AM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  6. #6
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    HI Xladept,

    I have tried, but there is only ("Head, Bi*") selected while the 1st & 2nd character ("BC, HEA*", "Deputy Hea*") not selected.

    I do not expect that filter would be a bit difficult task. If there is no star "*" sign, it works fine. we can use record macro code


    This code working fine
    Please Login or Register  to view this content.

    but will NOT working for filtering more than 2 items with star "*" sign
    Please Login or Register  to view this content.
    Last edited by Faridwahidi; 07-18-2014 at 07:15 PM.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: AutoFilter with multiple criteria in a single column

    If you can post a sample workbook, I'll take a look at it tomorrow.

  8. #8
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    Hi xladept,

    Attached herewith the with sample of workbooks.

    I have tried this code but only one item selected instead of 3.

    If I include Operator:=xlFilterValues, there is no item selected.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: AutoFilter with multiple criteria in a single column

    Hi Farid.

    I think I'm confused - when it filters the first criterion of the array, why would there be other criterion to filter on??

  10. #10
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    Hi Xladept,

    I want to filter Business Banking (1st Filter)

    then, within Business Banking, I want to select these criteria

    "BC, Head"
    "Deputy Head"
    "Head Biz Dev"

    I have decided to put star sign "*" in order to avoid missing items if the user add another words on that criteria. e.g. "BC, Head (Relief)"

    I really do not expect that filter would be a bit difficult to VBA beginner.
    I can use the recording macro code and add as much as criteria if there is no star "*" sign.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: AutoFilter with multiple criteria in a single column

    Hi Farid,

    I couldn't see anything wrong with your code, but here's a workaround:

    Please Login or Register  to view this content.

  12. #12
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    Hi Xladept,

    Thanks a lot. ,

    What does it means by j = 13

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: AutoFilter with multiple criteria in a single column

    Hi Farid,

    Thanks for the rep!

    j is the index for the For...Next Loop - the values start at 13 (the first data row) and go all the way to i

    BTW: If you want to recover between runs I should have added the code in red:

    Please Login or Register  to view this content.

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: AutoFilter with multiple criteria in a single column

    You can't use wildcards with an array filter, if you have more than two values in the array, which was the original problem.
    Last edited by romperstomper; 07-21-2014 at 09:34 AM. Reason: Expansion
    Remember what the dormouse said
    Feed your head

  15. #15
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    Hi romperstomper,

    This method works for me. Without the alternative method, can it be solved using standard filter?

  16. #16
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: AutoFilter with multiple criteria in a single column

    You'd have to loop and create an array of the actual values you are interested in, or use an advanced filter instead.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: AutoFilter with multiple criteria in a single column

    Hi Rory,

    Is it that:
    Please Login or Register  to view this content.
    is poorly coded??

  18. #18
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: AutoFilter with multiple criteria in a single column

    Hi XLAdept,
    As I indicated in my post #2, the wild card will not work with more 1 or 2(Can not remember exactly) criteria with filter, it acts like OR and AND in filter

  19. #19
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: AutoFilter with multiple criteria in a single column

    I guess that depends on your perspective.

    It's expecting exact values but it is willing to accept two 'contains' criteria, just as you could do through the UI.

  20. #20
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    Hi,

    Can anyone of you show example of advanced filter?

  21. #21
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: AutoFilter with multiple criteria in a single column

    Hi,

    Can anyone of you show example of advanced filter?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] FILTER single column with multiple criteria.
    By vij8y in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2014, 11:27 AM
  2. [SOLVED] Count text in one column Matching single or multiple Criteria from Other Column Excel 2003
    By Jose Macieira in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-17-2013, 06:55 AM
  3. Average Multiple Criteria Single Column
    By mma3824 in forum Excel General
    Replies: 2
    Last Post: 12-14-2012, 02:36 PM
  4. Replies: 6
    Last Post: 11-06-2011, 01:48 PM
  5. multiple column , multiple criteria autofilter sort
    By jseufert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-28-2009, 06:18 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1