Dynamic array in autofilter

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
80
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,713
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
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,523
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
80
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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...
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,523
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,713

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
.. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,487
Messages
5,625,054
Members
416,067
Latest member
newb08

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
Top