VBA autofilter not filtering table from array-CodePudding

I have an excel macro where I am trying to filter a table named "TrialBalance" based on the criteria loaded into an array. I can't figure out why it will only filter by the first value in the array.

I also need to reference the field name where the field is because the column location will change. I am a novice at excel vba, and have tried to use different code to get it to work, but it isn't working.

Sub CreatePremiumPvt()
    Dim arr As Variant
    Dim i As Integer, lrow As Long
    
    lrow = Sheets("2a.Premium").Cells(Rows.Count, "M").End(xlUp).Row
    arr = Sheets("2a.Premium").Range("M2:M" & lrow).Value
    
    Application.ScreenUpdating = False
    
    'Filter & Copy Premium Data from TB
    
    Sheets("TrialBalance").ListObjects("TrialBalance").Range.AutoFilter Field:=31, Operator:=xlFilterValues, Criteria1:=arr
    Sheets("TrialBalance").ListObjects("TrialBalance").Range.AutoFilter Field:=1, Criteria1:=Worksheets("Start Here").Range("C7").Value
    
    'add code to copy & paste columns to Premium tab
    
    Application.CalculateFull
    Application.ScreenUpdating = True
    
End Sub

CodePudding user response：

An array read from a range is not in a form that is recognised by AutoFilter: you need to transform it using (eg) Application.Transpose:

Sub CreatePremiumPvt()
    
    Dim arr As Variant
    
    With Worksheets("2a.Premium")
        arr = .Range("M2:M" & .Cells(Rows.Count, "M").End(xlUp).Row).Value
    End With
    
    Application.ScreenUpdating = False
    
    With Sheets("TrialBalance").ListObjects("TrialBalance")
        .Range.AutoFilter Field:=31, Operator:=xlFilterValues, _
                Criteria1:=Application.Transpose(arr)
        .Range.AutoFilter Field:=1, Criteria1:=Worksheets("Start Here").Range("C7").Value
    End With
    
    Application.CalculateFull
    Application.ScreenUpdating = True
    
End Sub