none
How to filter data on Excel using macros with multiple criteria RRS feed

  • Question

  • Hello!

    I'd like to ask were we can find information about filtering data on Excel with more than two criteria using macros.

    Greetings

    Wednesday, June 3, 2015 12:41 PM

Answers

  • Hi Aina,

    We can use Range.AautoFilter to filter the range with specific criteria we wanted. If there is two criterias, we can use pperator to construct compound criteria and here is sample for your reference:

    Set w = Worksheets("Sheet1")
    w.Cells.AutoFilter Field:=1, Criteria1:="Jack1", _
     Operator:=xlOr, Criteria2:="Jack2"
    

    And if the criterias are more than two, we can use array to achieve the goal. You can refer to the sample below:

      ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=1, Criteria1:=Array( _
            "Jack1", "Jack2", "Jack3"), Operator:=xlFilterValues

    The best way to learn how to navigate the object models of Word, Excel, and Powerpoint, based on what you want to do as a user, is to use the Macro Recorder.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 5, 2015 2:48 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Thursday, June 4, 2015 3:12 AM
  • Can you try it this way?

    http://www.techonthenet.com/excel/filters/advanced1.php


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, June 4, 2015 5:00 PM
  • Hi Aina,

    We can use Range.AautoFilter to filter the range with specific criteria we wanted. If there is two criterias, we can use pperator to construct compound criteria and here is sample for your reference:

    Set w = Worksheets("Sheet1")
    w.Cells.AutoFilter Field:=1, Criteria1:="Jack1", _
     Operator:=xlOr, Criteria2:="Jack2"
    

    And if the criterias are more than two, we can use array to achieve the goal. You can refer to the sample below:

      ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=1, Criteria1:=Array( _
            "Jack1", "Jack2", "Jack3"), Operator:=xlFilterValues

    The best way to learn how to navigate the object models of Word, Excel, and Powerpoint, based on what you want to do as a user, is to use the Macro Recorder.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 5, 2015 2:48 AM
    Moderator