Dynamic array in autofilter

[dotsent]

Hi guys! Taking my first steps in working with arrays. I'm trying to load a dynamic array as an autofilter criteria, but I must be missing something. Found some hints Googling around, but couldn't make it work. I will get the filter marker in the right column, but no filtering results. Array is loaded from "Helper_table" and running the filter in "Target_table"

Sub Filter_array()

Dim myArray() As Variant
Dim myTable As ListObject

Set myTable = ActiveSheet.ListObjects("Helper_table")
TempArray = myTable.DataBodyRange.Columns(1)
myArray = Application.Transpose(TempArray)

    With ActiveSheet.ListObjects("Target_table").Range  
      
    .AutoFilter Field:=7, Criteria1:=myArray, Operator:=xlFilterValues
    
    End With

End Sub

When checking array contents with MsgBox, I see the correct values presented, so the correct data seems to be loaded

MsgBox Join(myArray)

Or do I still need to run some maintenance on my values? Have I perhaps loaded the array incorrectly?

The filtering will work if I hardcode the array, however that's not really useful in this instance:

.AutoFilter Field:=7, Criteria1:=Array("5,92", "10", "6"), Operator:=xlFilterValues

 

[John_w]

Assigning a range to a Variant (your TempArray) creates a 2-dimensional array, even if the range consists of only 1 column or 1 row. However, the AutoFilter's Criteria1 argument expects a 1-dimensional array. This code therefore converts TempArray to the 1-dimensional myArray for use in the AutoFilter.

Public Sub Filter_array2()

    Dim myTable As ListObject
    Dim TempArray As Variant
    Dim myArray() As String
    Dim i As Long
    
    Set myTable = ActiveSheet.ListObjects("Helper_table")
    TempArray = myTable.DataBodyRange.Columns(1)
    
    ReDim myArray(1 To UBound(TempArray))
    For i = 1 To UBound(TempArray)
        myArray(i) = TempArray(i, 1)
    Next
    
    With ActiveSheet.ListObjects("Target_table").Range
        .AutoFilter Field:=7, Criteria1:=myArray, Operator:=xlFilterValues
    End With

End Sub

 

[igold]

How about this..

Sub Filter_array()


    Dim myArray() As Variant, TempArray As Variant
    Dim myTable As ListObject
    Dim i As Long


    Set myTable = ActiveSheet.ListObjects("Helper_table")
    TempArray = myTable.DataBodyRange.Columns(1)
    myArray = Application.Transpose(TempArray)
    For i = LBound(myArray) To UBound(myArray)
        myArray(i) = CStr(myArray(i))
    Next i
    With ActiveSheet.ListObjects("Target_table").Range
        .AutoFilter Field:=7, Criteria1:=myArray(), Operator:=xlFilterValues
    End With


End Sub

 

[Marcelo Branco]

The array must be a String array.

See if this helps
https://www.mrexcel.com/forum/excel...er-multiple-criteria-not-working-numbers.html

M.

 

[dotsent]

Thank you all! John, igold - both of your options worked fine.

However is there a way to make only values in visible rows to be loaded into array? As the name might suggest, I'm using the "Helper_table" to load values into array, however it's just an intermediate vessel since my original source is Table1. I need to load only visible data (after filtering) into array from Table1, but all I can manage right now is move visible data from Table1 to Helper_table and then load into array without exceptions.

Could there be an easy way to sidestep this? I have been playing around with

.SpecialCells(xlCellTypeVisible)

but to no avail...

 

[igold]

Is this what you are looking for...

Sub Filter_array()


    Dim myArray() As Variant, TempArray As Variant
    Dim myTable As ListObject
    Dim i As Long
    Dim rng As Range
    Dim arr
    
    Set myTable = ActiveSheet.ListObjects("Helper_table")
    TempArray = myTable.DataBodyRange.Columns(1)
    myArray = Application.Transpose(TempArray)
    For i = LBound(myArray) To UBound(myArray)
        myArray(i) = CStr(myArray(i))
    Next i
    With ActiveSheet.ListObjects("Target_table").Range
        .AutoFilter Field:=7, Criteria1:=myArray(), Operator:=xlFilterValues
    End With
    Set rng = ActiveSheet.ListObjects("Target_table").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
    arr = rng
    
End Sub

 

[John_w]

This macro puts the visible values from column 1 in Table1 into myArray and then filters column 7 of Target_table with that array.

Public Sub Filter_with_Array()

    Dim Table1 As ListObject
    Dim cell As Range
    Dim myArray() As String
    Dim i As Long
    
    Set Table1 = ActiveSheet.ListObjects("Table1")
    
    i = 0
    For Each cell In Table1.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
        ReDim Preserve myArray(i)
        myArray(i) = cell.Value
        i = i + 1
    Next
    
    With ActiveSheet.ListObjects("Target_table").Range
        .AutoFilter Field:=7, Criteria1:=myArray, Operator:=xlFilterValues
    End With

End Sub

 

[dotsent]

Brilliant, thank you

 

[Peter_SSs]

.. another slightly different way without needing to consider the dimension of the array to use with the filter.

Sub UseSameFilterValues()
  Dim cell As Range
  Dim s As String
  
  For Each cell In ActiveSheet.ListObjects("Helper_table").DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
    s = "|" & cell.Value & s
  Next cell
  ActiveSheet.ListObjects("Target_table").Range.AutoFilter Field:=7, Criteria1:=Split(Mid(s, 2), "|"), Operator:=xlFilterValues
End Sub