excelvbaexcel-tableslistobject

How to use a Named Range/Array to AutoFilter a Table?


I am using the below code to set an array to the values in a Named Range (containing account numbers) and then use that array as the criteria to AutoFilter a table (Table1).

Sub FilterRangeCriteria()

Dim vCrit As Variant
Dim rngCrit As Range

Set rngCrit = wsL.Range("CritList")

vCrit = rngCrit.Value
wsO.ListObjects("Table1").Range.AutoFilter _
    Field:=8, _
    Criteria1:=vCrit, _
    Operator:=xlFilterValues
End Sub

I can see that the array contains all of the values from the named range however the table that I'm trying to filter will eliminate all rows below the header and not show any rows with the criteria from the named range.

Table that needs to be filtered.

Named Range in column.


Solution

  • This will work if CritList is a single column or row. Otherwise, you'll have to create a 1D array from the values.

    Sub FilterRangeCriteria()
    
        Dim vCrit As Variant
        Dim rngCrit As Range
    
        Set rngCrit = wsL.Range("CritList")
    
        vCrit = WorksheetFunction.Transpose(WorksheetFunction.Unique(rngCrit))
        wsO.ListObjects("Table1").Range.AutoFilter _
            Field:=8, _
            Criteria1:=vCrit, _
            Operator:=xlFilterValues
    End Sub
    

    EDIT

    For the filter to work properly, the numeric values need to be converted to strings.

    Sub FilterRangeCriteria()
    
        Dim vCrit As Variant
        Dim rngCrit As Range
    
        Set rngCrit = wsL.Range("CritList")
    
        vCrit = WorksheetFunction.Transpose(WorksheetFunction.Unique(rngCrit))
        
        Rem Numeric filter values need to be converted to strings
        Dim n As Long
        For n = LBound(vCrit) To UBound(vCrit)
            vCrit(n) = CStr(vCrit(n))
        Next
        
        wsO.Range("A11").CurrentRegion.AutoFilter Field:=8, Criteria1:=vCrit, Operator:=xlFilterValues
    End Sub