Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

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

about 4 years ago · Santiago Trujillo
2 answers
Answer question

0

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
about 4 years ago · Santiago Trujillo Report

0

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
about 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!