Geek asks and answers

Filter table in excel VBA based on value column

I have a table, and I would like to filter rows by values ​​in one of my columns. The values ​​used for filtering are stored in a separate column that is not contained in the table. This is what I still have:

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 according to the criteria1. I know that it must be an array, and I can set it as something like Array ("12", "2", "13"), but I need it to equal the values ​​specified in the range given by range1. Any help would be greatly appreciated.

EDIT: I managed to get the range values ​​in the array by doing range1.Value and then converting Variant to an array of strings. This did not work as I wanted, as it just sets my filter to the last value in my array. For example, if my array contains identifiers ("12", "44", "13", "22"), and I install Criteria1 into this array and run it, the filter has only 22, and all other numbers are canceled, including 12, 44 and 13.

+5
source share
1 answer

! , , , - , . , , , - . :

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

": = xlFilterValues" , ,

+12

More articles:


All Articles