Share via

VBA Excel Advanced Filter with multiple criteria selected for 1 row

Anonymous
2018-02-13T12:21:23+00:00

Sheet1 contains my data which also becomes my filtered data as i have set filter in place rather than copying the range.

The criteria range on the spreadsheet is populated by a UserForm command button, which also applies the advanced filter.

The criteria range of 2 of the columns within this filter are populated from a list box with the MultiSelectMulti function enabled.

I want to be able to select multiple items from these 2 lists boxes to filter for. I have tried the following and it populated the cells i assigned as it should. Although the filter i believe is trying to find all the values i have assigned in one row, not for each individually so there-fore not displaying anything.

    'SEARCH CRITERIA - JOB STATUS

    If ListBox1.Selected(0) = True Then Range("BK2") = "WON"

    If ListBox1.Selected(1) = True Then Range("BK3") = "PENDING"

    If ListBox1.Selected(2) = True Then Range("BK4") = "LOST"

    'SEARCH CRITERIA - WIN PERCENTAGE

    If ListBox2.Selected(0) = True Then Range("BL2").Value = "100%"

    If ListBox2.Selected(1) = True Then Range("BL3").Value = "90%"

    If ListBox2.Selected(2) = True Then Range("BL4").Value = "80%"

    If ListBox2.Selected(3) = True Then Range("BL5").Value = "70%"

    If ListBox2.Selected(4) = True Then Range("BL6").Value = "60% OR LESS"

    'APPLY ADVANCED FILTER USING SELECTED CRITERIA

       Range("A6:BD99999").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

            Range("BH1:BL6")

    'BH1:BJ2 CONTAINS MY OTHER CRITERIA

I already have the sheet set to unprotect prior the code and after the code (as well as my other selections on the user form which work fine). I have tried using 'OR' and 'Else:' to no avail.

Any suggestions would be greatly appreciated on how i can solve my issue to filter the above when selecting multiple items without me having to create extra columns for each criteria in the data as i will have to move loads of conditional formatting manually and it will create too much clutter on my already large sheet.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-02-14T02:57:15+00:00

    OneDrive link to my spreadsheet

    https://1drv.ms/x/s!Aj2gaKewfUGjdCJtA7SPH8AENAE

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2018-02-14T02:40:47+00:00

    I need to have a look on your workbook to provide you the right solution.

    Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link? It will help me to give prompt and high quality solution.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-02-14T02:37:58+00:00

    It's saying my criteria range is invalid when selecting rows after the header and first row.

    I have changed my code for the criteria range to include these on the 2nd row next to the other critera as per my following code.

    BJ1:BL6 will not work for my advanced filter as i have criteria for other data in BH1:BJ2.

        Private Sub CommandButton1_Click()

        'SEARCH CRITERIA - JOB NUMBER

        Range("BH2").Value = TextBox1.Value

        'SEARCH CRITERIA - CLIENT

        Range("BI2").Value = TextBox2.Value

        'SEARCH CRITERIA - JOB ADDRESS

        Range("BJ2").Value = TextBox3.Value

        'SEARCH CRITERIA - JOB STATUS

        If ListBox1.Selected(0) = True Then Range("BK2") = "WON"

        If ListBox1.Selected(1) = True Then Range("BL2") = "PENDING"

        If ListBox1.Selected(2) = True Then Range("BM2") = "LOST"

        'SEARCH CRITERIA - WIN PERCENTAGE

        If ListBox2.Selected(0) = True Then Range("BN2").Value = "100%"

        If ListBox2.Selected(1) = True Then Range("BO2").Value = "90%"

        If ListBox2.Selected(2) = True Then Range("BP2").Value = "80%"

        If ListBox2.Selected(3) = True Then Range("BQ2").Value = "70%"

        If ListBox2.Selected(4) = True Then Range("BR2").Value = "60% OR LESS"

        'APPLY ADVANCED FILTER USING SELECTED CRITERIA

        Range("A6:BD99999").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

               Range("BH1:BR2")

        'UNLOAD ADD JOBS USERFORM

        Unload Me

        End Sub

    Was this answer helpful?

    0 comments
  4. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2018-02-13T13:05:44+00:00

    Can you see if your Advanced Filter is exactly working by putting them in a sheet and without VBA code?

    I believe that your criteria range has to be

    BJ1:BL6 not BH1:BL6

    Was this answer helpful?

    0 comments