Filter table in excel VBA based on a column of values

Question

I have a table and I would like to filter the rows by the values in one of its columns. The values used to filter are stored in a separate column not in the table. This is what I have so far:

Dim table1 As ListObject
Dim range1 As Range
Set range1 = ActiveSheet.range("AM23:AM184")
'get table object
table1.range.AutoFilter Field:=3, Criteria1:=???

I do not know what to put for criteria1. I know it needs to be an Array and I can set it to something like Array("12","2","13") but what I need it to equal is the values specified in the range given by range1. Any help would be greatly appreciated.

EDIT: I have been able to get my range values into an Array by doing range1.Value and then converting the Variant into a string array. This did not work as I wanted it to as it just sets my Filter to the last value in my array. For instance, if my array contains the IDs ("12","44","13","22") and I set Criteria1 to that array and run it, the filter only has 22 selected and all other numbers are deselected including 12, 44, and 13.

have you tried recording the Autofilter to get some example code? — Our Man in Bananas
– Our Man in Bananas, Commented Apr 12, 2013 at 17:56
yeah, I got similar code above but it just put an Array object in for Criteria1 with the values I deselected as entries in the Array object — user972276
– user972276, Commented Apr 12, 2013 at 17:59
I dont know how to do the above manually either is the problem — user972276
– user972276, Commented Apr 12, 2013 at 17:59

user972276 · Accepted Answer · 2013-04-12 20:12:31Z

18

I figured it out! I had tried recoding but the first time I tried it, it gave me an incomplete program due to too many lines associated with one line of code. So I redid the recording to give me the whole code and turns out I was missing something. Here is the whole code:

Dim range1 As range
Set range1 = ActiveSheet.range("AM23:AM184")
Dim var1 As Variant
Dim sArray() As String
Dim i As Long
var1 = range1.Value

ReDim sArray(1 To UBound(var1))

For i = 1 To (UBound(var1))
    sArray(i) = var1(i, 1)
Next

ActiveSheet.ListObjects("Table1").range.AutoFilter Field:=3, Criteria1:=sArray, Operator:=xlFilterValues

the "Operator:=xlFilterValues" was the key part I missed from recording the macro the first time because the recording stopped pre-maturely

answered Apr 12, 2013 at 20:12

user972276

3,0739 gold badges37 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter table in excel VBA based on a column of values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related