Advanced Filter with multiple criteria selected for 1 row

Question

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.

I am pretty new at using VBA and have read some posts about using Unique:=True after the code for the criteria range. I don't know how to use this so if someone could explain that would be great.

'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")

'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.

Answer 1

in short, the AdvancedFilter filtering criteria requires to:

• spread filter values between rows to achieve some "OR" condition

• keep filter values in the same row to achieve some "AND" condition

Not so sure about your actual filtering needs, but my first guess is that you need something like follows (explanations in comments, and I'm assuming that filtering criteria are contained in listboxes items themselves):

Option Explicit

Private Sub CommandButton1_Click()
    Dim iSel As Long, iRow As Long

    Intersect(Range("BH1:BR1").EntireColumn, ActiveSheet.UsedRange).Offset(1).ClearContents ' clear any existing filtering criteria

    With ListBox1 'reference ListBox1
        For iSel = 0 To 2 'loop through its items from first to third (note you can use 'For iSel = 0 To .Count-1' to loop through all its items)
            If .Selected(iSel) Then ' if current item is selected
                iRow = iRow + 1 'update filter range row to achieve "OR" condition
                Cells(1 + iRow, "BK") = .List(iSel) 'write current referenced listbox value in criteria range cell in a row by its own
            End If
        Next
    End With

    With ListBox2
        For iSel = 0 To 4
            If .Selected(iSel) Then
                iRow = iRow + 1
                Cells(1 + iRow, "BN") = .List(iSel)
            End If
        Next
    End With

    Range("A6:BD99999").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("BH1:BR1").Resize(iRow + 1) ' size criteria range to fit the filtering values read from listboxes
End Sub

in such a way you filter A:BD rows that match any filter value between those selected in the two listboxes, i.e. filtered record will have:

• any selected value from ListBox1 in column referenced by "BH1" cell value

or

• any selected value from ListBox2 in column referenced by "BN1" cell value

should that be what you need, than you can remove columns BL to BM and BO to BR from criteria range (and therefore adjust all BN references to BL)

finally I'd recommend you to use explicit worksheet reference instead of implicitly relying (as your code currently does) on the ActiveSheet