Dynamic array in autofilter

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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"

Code:
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
Code:
MsgBox Join(myArray)

array.jpg
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:
Code:
.AutoFilter Field:=7, Criteria1:=Array("5,92", "10", "6"), Operator:=xlFilterValues
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.

Code:
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
 
Upvote 0
How about this..

Code:
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
 
Upvote 0
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
Code:
.SpecialCells(xlCellTypeVisible)

but to no avail...
 
Upvote 0
Is this what you are looking for...

Code:
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
 
Upvote 0
This macro puts the visible values from column 1 in Table1 into myArray and then filters column 7 of Target_table with that array.
Code:
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
 
Upvote 0
.. another slightly different way without needing to consider the dimension of the array to use with the filter.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top