Autofilter two columns with same array

[vegas808]

I can autofilter first column with an array with no problem. Trying to autofilter with two columns, in a table, using same array values. Same values in different columns are never on the same row. Tried searching around for a solution.

working if when used separately

ws.ListObjects("tblA").Range.AutoFilter Field:=2, Criteria1:=arr, Operator:=xlFilterValues
ws.ListObjects("tblA").Range.AutoFilter Field:=5, Criteria1:=arr, Operator:=xlFilterValues

Below doesn't work for me.

ws.ListObjects("tblA").Range.AutoFilter Field:=2, Criteria1:=arr, Operator:=xlOr, _
ws.ListObjects("tblA").Range.AutoFilter Field:=5, Criteria1:=arr, Operator:=xlFilterValues

 

[Peter_SSs]

You have only given us part of your code and whilst your code refers to a ListObject, your image does not really look like one as the columns don't appear to have headings.
To filter as it seems you wish, you would need to either ..

1. Add an extra helper column to the table and AutoFilter using that or
2. Use Advanced Filter instead of AutoFilter

I have opted for 2 above and started with this

vegas808.xlsm
	A	B	C	D	E	F	G	H
1
2	Column1	Column2
3	A1						B1
4	A2						B2
5	A3						B3
6	B1	A1
7	B2	A2
8	B3	A3
9	C1	B1
10	C2	B2
11	C3	B3
12		C1
13		C2
14		C3
15
16
Sheet1

I then used this code ..

Sub vegas808()
  Dim rCrit As Range
  Dim arrAddr As String
  
  arrAddr = Range("G3", Range("G" & Rows.Count).End(xlUp)).Address
  Set rCrit = Range("H3:H4")
  rCrit.Cells(2).Formula2 = Replace("=OR(ISNUMBER(MATCH(" & Range("tblA").Cells(1, 1).Address(0, 0) & ",#,0)),ISNUMBER(MATCH(" & Range("tblA").Cells(1, 2).Address(0, 0) & ",#,0)))", "#", arrAddr)
  Range("tblA[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  rCrit.ClearContents
End Sub

.. to finish with this. Is that any use to you?

vegas808.xlsm
	A	B	C
1
2	Column1	Column2
6	B1	A1
7	B2	A2
8	B3	A3
9	C1	B1
10	C2	B2
11	C3	B3
15
16
Sheet1

 

[vegas808]

Peter,

Sorry for the confusion. It was late at night and dozed off quickly soon after posting. Here's my test macro that I wanted to learn more about using autofilter so I can apply it somewhere else. Wanted to get away from For loops.

Sub autofilter2cols()

Dim ws As Worksheet: Set ws = Worksheets("Test")
Dim tblA As ListObject: Set tblA = ws.ListObjects("tblInventory")
Dim arr(), i As Variant
Dim lastrw As Integer: lastrw = ws.Range("G" & Rows.Count).End(xlUp).Row

tblA.AutoFilter.ShowAllData

'arr list will be relocated later
arr = Application.Transpose(ws.Range("G3:G" & lastrw))

For i = LBound(arr) To UBound(arr)
arr(i) = CStr(arr(i))
Next i

'Fix code here. One work but both don't
tblA.Range.AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
' tblA.Range.AutoFilter Field:=2, Criteria1:=arr, Operator:=xlFilterValues

End Sub


Your code works and even shorter. Thank you, Peter! Now I gotta look into advanced filtering. Sorry, still learning new things everyday.

 

[vegas808]

Peter,

I'm trying to use your code with using a Table, instead of a Range("tblA"). I'm failing miserably. Erroring out at red line

Sub vegas808()
Dim rCrit As Range
Dim arrAddr As String
Dim ws As Worksheet: Set ws = Worksheets("Test")
Dim tblA As ListObject: Set tblA = ws.ListObjects("tblInventory")

' ActiveSheet.ShowAllData
arrAddr = Range("G3", Range("G" & Rows.Count).End(xlUp)).Address
Set rCrit = Range("H3:H4")
rCrit.Cells(2).Formula2 = Replace("=OR(ISNUMBER(MATCH(" & tblA.Cells(1, 1).Address(0, 0) & ",#,0)),ISNUMBER(MATCH(" & tblA.Cells(1, 2).Address(0, 0) & ",#,0)))", "#", arrAddr)
Range("tblA[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
rCrit.ClearContents

ActiveSheet.ShowAllData

End Sub

 

[vegas808]

Disregard previous post. Don't know how to delete it.