Filter a table for each row from another table

Question

What I need to do is filter through a table by using the second column from each row from another table. The table that I loop through contains 11 rows and the code loops 11 times, but it uses the 11th row each time. What it needs to do is go from the first row to the last row.

I have used this as source

Edit: With help of Variatus I have applied a count instead of For Each. This seems to be able to go through all the rows, except the last row. To be continued.

Edit2: for those who are curious, have a look at my follow up question where I got everything to work! Filter a table with an array of criteria derived from another table

Sub LoopDoorAfdelingV4()

Dim myTable As ListObject
Dim myTable2 As ListObject
Dim oRow As ListRow
Dim c As Long


Dim myGroupIDFilter As Variant
Dim myGroupNameFilter As Variant

Set myTable = ActiveSheet.ListObjects("TabelGroupID")
Set myGroupIDFilter = myTable.ListColumns(1).Range
Set myGroupNameFilter = myTable.ListColumns(2).Range
Set myTable2 = ActiveSheet.ListObjects("TabelAfdelingenIntern")


For c = 1 To myTable.ListRows.Count

ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=myGroupNameFilter(c), _
 Operator:=xlOr
Next c

End Sub

I fear that it does indeed apply 11 filters, one after the other, but you only get to see the last because the code doesn't stop before. What you presumably wish to program is one filter with 11 criteria. You may find some guidance here: https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba — Variatus, Jan 18 '19 at 09:23
Thanks for your answer. That might be a last resort, because I programmed it this way so that the table of 11 rows can be expanded without changing the code. I need this code to look at row one, then apply the filter and then go to the next row untill the last row. — Joey b, Jan 18 '19 at 09:27

Variatus · Accepted Answer · 2019-01-18T12:02:50.033

Consider setting up the filtering code with the maximum number of criteria you wish to be able to handle, like this (here demonstrated with just 3),

ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=myGroupNameFilter(1), _
Criteria2:=myGroupNameFilter(2),Criteria3:=myGroupNameFilter(3),Operator:=xlOr

Set the UBound for myGroupNameFilter to that same maximum number. Use the loop reading the criteria to fill the myGroupNameFilter array. Fill the elements of myGroupNameFilter for which you have no values with random values which you know can't exist in the list to be filtered, perhaps "XYZ987" or -99999. The filter should return the specified result because those criteria which find no match will not influence the result.

I hate to publish this code because it is sure not to work and I have no way of testing. It is intended to merely show the idea.

Dim Flt(1 To 15) As Variant

For i = 1 To 15
    If i > myTable.ListRows Then
        ' a value which doesn't exist in your Table2
        Flt(i) = "123XYZ"
    Else
        ' assign a value on which you intend to filter
        Flt(i) = myTable.ListRows(i).Cells(1).Value
    End If
Next

ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=Flt(1), _
                                                 Criteria2:=Flt(2), _
                                                 Criteria3:=Flt(3), _
                                                 Criteria4:=Flt(4), _
                                                 Criteria5:=Flt(5), _
                                                 Criteria6:=Flt(6), _
                                                 Criteria7:=Flt(7), _
                                                 Criteria8:=Flt(8), _
                                                 Criteria9:=Flt(9), _
                                                 Criteria10:=Flt(10), _
                                                 Criteria11:=Flt(11), _
                                                 Criteria12:=Flt(12), _
                                                 Criteria13:=Flt(13), _
                                                 Criteria14:=Flt(14), _
                                                 Criteria15:=Flt(15), _
                                        Operator:=xlOr

The point is that you can't set the filter in the loop in which you set the filter criteria.

your comment gave me an idea. Isn't possible to make something like the following where I count within the criteria?`ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=myGroupNameFilter(oRow + 1), _ Operator:=xlOr` — Joey b, Jan 18 '19 at 10:58
You need a separately enumerated Criteria for each additional filter, meaning you need Criteria1, Criteria2, Critera3 etc. The array myGroupNameFilter must be indexed to match the Criteria enumeration. Perhaps myGroupNameFilter(CountA) = oRow.Range(1) will work. I think For Each oRow is unfortunately because you have to count rows in addition. For R = 1 To ListRows.Count might be better. Then you might use myGroupNameFilter(R) = MyTable.ListRows(R).Cells(1).Value (syntax not tested). — Variatus, Jan 18 '19 at 11:16
This code actuallly works but only seems to count untill 10 and not the 11th and thus the last row. But we are getting there! `For c = 1 To myTable.ListRows.Count ActiveSheet.Range(myTable2).AutoFilter Field:=1, Criteria1:=myGroupNameFilter(c), _ Operator:=xlOr Next c` — Joey b, Jan 18 '19 at 11:48
When I add a dummy row (that will not give results) it does count till the row I want it to. Thus it works. Thank for the help! I am however not finished yet. The table that is filtered now gives 2 to 5 criteria I need to use as filter for another table. Thus I need to apply a list of criteria to another table. Any suggestions? — Joey b, Jan 18 '19 at 11:58
Not here Joe, and not now. Let's get the problem you first had solved here and the rest in another thread - tomorrow. :-) — Variatus, Jan 18 '19 at 12:05

Filter a table for each row from another table

1 Answers1

Linked