VBA filtering

[DBIO]

Hi experts,


What I'm trying to complete is beyond my Excel-skills so you you guys can help.

I have a table, where i need to filter basically on four column, but the values on these I have textjoined with semicolon, collecting these values in one cell (in column AW). This value illustrated how many weeks there is until deadline.

I would like to create six (6) filters in VBA filters, to look in this column AW and do the following:
- First filter: If cell contains the value 0 or 1 (indicating deadline this week or next), show the rows.
- Second filter: If cell contains the value 0, 1 or 2(indicating deadline this week, next or the week after), show the rows.
- The next filters are with the same principals as above, just adding one extra week in the end.

My skills with VBA only allows me to filter with two criteria’s (which as I understand is also the normal limitation for this).

Do any of you have any ideas on how to approach this? Thanks in advance.

 

[ChrisFoster]

Try:

Sub ApplyFilters()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim filterCriteria(1 To 6) As String
    
    filterCriteria(1) = "0;1"
    filterCriteria(2) = "0;1;2"
    filterCriteria(3) = "0;1;2;3"
    filterCriteria(4) = "0;1;2;3;4"
    filterCriteria(5) = "0;1;2;3;4;5"
    filterCriteria(6) = "0;1;2;3;4;5;6"
    
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the name of your worksheet
    Set tbl = ws.ListObjects("Table1") ' Replace "Table1" with the name of your table
    Set rng = tbl.ListColumns("AW").DataBodyRange ' Replace "AW" with the column name of your range
    
    For i = 1 To 6
        tbl.Range.AutoFilter Field:=rng.Column, Criteria1:=Split(filterCriteria(i), ";"), Operator:=xlFilterValues
        ' Perform any additional operations you want to do here, such as copying the filtered data to another worksheet
    Next i
    
    tbl.Range.AutoFilter Field:=rng.Column

End Sub