Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

440
Visualizações
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 Respostas
Responde à pergunta

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 Relatório

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda