Como filtrar tabelas dinâmicas usando vba (com exemplos)

Você pode usar os seguintes métodos para filtrar tabelas dinâmicas no Excel usando VBA:

Método 1: Filtre a tabela dinâmica com base em um valor

 SubFilterPivotTable ()
   Dim pf As PivotField
   Dim myFilter As String
   Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
   myFilter = ActiveWorkbook.Sheets(" Sheet1 ").Range(" J2 ").Value
   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub

Esta macro específica filtrará a tabela dinâmica chamada PivotTable1 para mostrar apenas as linhas onde o valor na coluna Posição da tabela dinâmica é igual ao valor na célula J2 de Sheet1 .

Método 2: Filtrar a Tabela Dinâmica com base em vários valores

 Sub FilterPivotTableMultiple()
  Dim v Ace Variant
  Dim i As Integer, j As Integer
  Dim pf As PivotField
  Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
  
  'specify range with values to filter on
  v = Range(" J2:J3 ")
  
  'clear existing filters
  pf.ClearAllFilters
  
  'apply filter to pivot table
  With pf
    For i = 1 TB pf.PivotItems.Count
      j = 1
      Do While j <= UBound(v, 1) - LBound(v, 1) + 1
         If pf.PivotItems(i).Name = v(j, 1) Then
           pf.PivotItems(pf.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pf.PivotItems(pf.PivotItems(i).Name).Visible = False
       End If
        j = j + 1
      Loop
    Next i
 End With
End Sub

Esta macro específica filtrará a tabela dinâmica chamada Tabela Dinâmica1 para exibir apenas as linhas onde o valor na coluna Posição da tabela dinâmica é igual a um dos valores no intervalo de células J2:J3 .

Método 3: remover filtros de tabela dinâmica

 SubClearPivotTableFilter ()
   Dim pt As PivotTable
   Set pt = ActiveSheet.PivotTables(" PivotTable1 ")
   pt.ClearAllFilters
End Sub

Esta macro específica limpará todos os filtros da tabela dinâmica chamada PivotTable1 .

Os exemplos a seguir mostram como usar cada um desses métodos na prática.

Exemplo 1: Filtre a tabela dinâmica com base em um valor

Digamos que criamos uma tabela dinâmica a partir de um conjunto de dados no Excel para resumir os pontos marcados por jogadores de basquete de diferentes times e posições:

Digamos que queremos filtrar a tabela dinâmica para mostrar apenas as linhas cujo valor na coluna Posição é Guarda.

Podemos criar a seguinte macro para fazer isso:

 SubFilterPivotTable ()
   Dim pf As PivotField
   Dim myFilter As String
   Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
   myFilter = ActiveWorkbook.Sheets(" Sheet1 ").Range(" J2 ").Value
   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub

Quando executamos esta macro, a tabela dinâmica é automaticamente filtrada para mostrar apenas as linhas cujo valor na coluna Posição é Guarda:

A tabela dinâmica foi filtrada para exibir apenas as linhas cujo valor na coluna Posição é Guarda.

Exemplo 2: Filtre uma tabela dinâmica com base em vários valores

Suponha que, em vez disso, desejemos filtrar a tabela dinâmica para mostrar apenas as linhas cujo valor na coluna Posição é Guarda ou Centro.

Podemos criar a seguinte macro para fazer isso:

 Sub FilterPivotTableMultiple()
  Dim v Ace Variant
  Dim i As Integer, j As Integer
  Dim pf As PivotField
  Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
  
  'specify range with values to filter on
  v = Range(" J2:J3 ")
  
  'clear existing filters
  pf.ClearAllFilters
  
  'apply filter to pivot table
  With pf
    For i = 1 TB pf.PivotItems.Count
      j = 1
      Do While j <= UBound(v, 1) - LBound(v, 1) + 1
         If pf.PivotItems(i).Name = v(j, 1) Then
           pf.PivotItems(pf.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pf.PivotItems(pf.PivotItems(i).Name).Visible = False
       End If
        j = j + 1
      Loop
    Next i
 End With
End Sub

Quando executamos esta macro, a tabela dinâmica é automaticamente filtrada para mostrar apenas as linhas cujo valor na coluna Posição é Guarda ou Centro:

A tabela dinâmica foi filtrada para mostrar apenas as linhas cujo valor na coluna Posição é Guarda ou Centro.

Recursos adicionais

Os tutoriais a seguir explicam como realizar outras tarefas comuns em VBA:

VBA: como atualizar tabelas dinâmicas
VBA: como remover valores duplicados
VBA: Como contar o número de linhas no intervalo