none
Autofiltering multiple columns with different constraints at the same time. RRS feed

  • Question

  • Hi,

    I'm trying to filter columns J-->AA with <3.0 and AB->AE with<9.3.

    But with the code below, once a second column filters... then there are no results shown

    because values <3 or 9.3 only occur once per row. Is there a better way to find and filter

    these? Is it possible to then write code to eliminate the filters when I finish recording

    the filtered values? Thanks for your help!

    Worksheet("Shingle Samples").Range("J15:AA1000").Column ' Autofilter the criteria ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=1, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=2, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=3, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=4, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=5, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=6, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=7, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=8, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=9, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=10, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=11, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=12, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=13, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=14, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=15, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=16, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=17, Criteria1:="<3" ActiveSheet.Range("$J$13:$AA$1000").AutoFilter Field:=18, Criteria1:="<3"


    Friday, October 21, 2016 10:15 PM

All replies

  • 1)The code will show only the row where all column from 1 to 18 is set to value "<3".Do you want that ?

    Or you want to show the rows if any column is set to above value.

    2)Below will clear all filter from active sheet.

    Activesheet.AutoFIlter.Showalldata


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Saturday, October 22, 2016 10:44 AM
    Answerer
  • Hi Zachman,

    >> I'm trying to filter columns J-->AA with <3.0 and AB->AE with<9.3.

    For adding multiple filter, you need to add “Operator:=xlAnd”.

    Here is a simple code:

        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$B$9").AutoFilter Field:=1, Criteria1:="<3", _
            Operator:=xlAnd
        ActiveSheet.Range("$A$1:$B$9").AutoFilter Field:=2, Criteria1:=">14", _
            Operator:=xlAnd

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 24, 2016 5:19 AM
  • Thanks for the fast reply Asadulla,

    1. I want all rows to be shown with the criteria of <3 for range (J15:AA1000) and <9.3 for range (AB15:AR1000).

    2. Works great! Thanks!


    3. Please see my note to Edward below for further clarification.
    • Edited by Zachman Do Monday, October 24, 2016 9:48 PM
    Monday, October 24, 2016 9:40 PM
  • Hi Edward,

    Thanks for your code. I tried it out. It appears only one row is shown with this filter. My data starts in row 15, columns J-->AA and AB-->AE. Each row has the possibility of having a number meeting the criteria below in a total of 23 columns. I want the rows that meet the criteria in one of the columns to remain filtered while the other columns are also filtered. Thus producing a list of rows with defects that could occur in 1-->23 columns.

    Selection.AutoFilter
        ActiveSheet.Range("$J$15:$AA$1000").AutoFilter Field:=1, Criteria1:="<3", _
            Operator:=xlAnd
        ActiveSheet.Range("$AB$15:$AE$1000").AutoFilter Field:=2, Criteria1:="<9.3", _
            Operator:=xlAnd

    Monday, October 24, 2016 9:47 PM
  • Hi Zachman,

    Do you mean my code did not work for you? If so, I suggest you try to record the code by below steps, Developer->Record Macro->Select the Range you want to filter->Home->Editing->Filter->Set the Criteria for the fields as your requirement->Stop Record->Check the generated code.

    If it did not work for you, it would be helpful if you could share us your data in this range.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 25, 2016 2:39 AM
  • Auto Filter on multiple column works like AND. It will show you the rows where all columns matches the criteria.

    Say in J16:AA16, all cell is <3, then it will be shown. But it seems you need to show any row which has <3 in any column.

    If that is the case, go for Advanced Filter. Refer offline help for that in excel by pressing F1.

    a) Need two cell in same sheet. Say it XF1 and XF2. Keep XF1 blank. In XF2 paste below formula..

     =COUNTIF(J15:AA15,"<3")>=1

     Assume your actual data starts at J15, if it is J16 then formula will be

     =COUNTIF(J16:AA16,"<3")>=1

     Note:We need only first row of data for formula.

    b) Data->Advanced Filter->Select List Range (This is your all data including heading)->Select Criteria range as Xf1:XF2->OK

    That will do the job. Let us know if that works.

    We can later add second condition easily. And then transfer finally to VBA. But first need to do in user interface.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, October 25, 2016 4:06 AM
    Answerer