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

user_18227903 0
Viewed 52

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.

vba excel
2 Answers

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
user_99127140

Filter Excel Table Column on Values of a Named Range

Sub FilterRangeCriteria()
    
    ' Source - read criteria list
    ' 'wsL' is the code name of a worksheet containing this code
    Const sRangeName As String = "CritList"
    
    ' Destination - filter a table
    ' 'wsO' is the code name of a worksheet containing this code
    Const dtblName As String = "Table1"
    Const dlcName As String = "Ledger Account"
    
    ' Keep in mind that this would fail on 'UBound(cData, 1)'
    ' if there was only one cell in the named range.
    Dim cData As Variant: cData = wsL.Range(sRangeName).Value
    
    ' "AutoFilter" 'likes' strings and the array needs to be 1D,
    ' hence write the values from the 2D one-based one-column criteria array,
    ' converted to strings, to a 1D one-based string array ('sArr').
    
    Dim sArr() As String: ReDim sArr(1 To UBound(cData, 1))
    
    Dim r As Long
    
    For r = 1 To UBound(cData, 1)
        sArr(r) = CStr(cData(r, 1))
    Next r
    
    ' Reference the destination table.
    Dim dtbl As ListObject: Set dtbl = wsO.ListObjects(dtblName)
    
    Application.ScreenUpdating = False
    
    ' Remove any filters.
    If dtbl.ShowAutoFilter Then
        If dtbl.AutoFilter.FilterMode Then dtbl.AutoFilter.ShowAllData
    Else
        dtbl.ShowAutoFilter = True
    End If

    ' Reference the destination table criteria column.
    Dim dlc As ListColumn: Set dlc = dtbl.ListColumns(dlcName)
    
    ' Apply the filter.
    dtbl.Range.AutoFilter dlc.Index, sArr, xlFilterValues

    Application.ScreenUpdating = True
    
    ' Inform.
    MsgBox "Table filtered.", vbInformation

End Sub
user_98140690
Related