VBA – Filtro de Tabela Dinâmica

Este tutorial demonstrará como usar o filtro de tabela dinâmica no VBA.

As tabelas dinâmicas são uma ferramenta de dados excepcionalmente poderosa do Excel. As tabelas dinâmicas nos permitem analisar e interpretar grandes quantidades de dados agrupando e resumindo campos e linhas. Podemos aplicar filtros às nossas tabelas dinâmicas para que possamos ver rapidamente os dados que são relevantes para nós.

Em primeiro lugar, precisamos criar uma tabela dinâmica para nossos dados. (Clique aqui para ver nosso Guia de tabela dinâmica VBA)

Criação de um Filtro com Base em um Valor de Célula

Neste exemplo, filtraremos uma tabela dinâmica com base em um valor de célula.

Em uma célula vazia à direita da tabela dinâmica, crie uma célula para conter o filtro e, em seguida, digite os dados na célula em que deseja filtrar a tabela dinâmica.

Crie a seguinte macro VBA:

Sub FiltroValorPagina()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Fornecedor")
   strFilter = ActiveWorkbook.Sheets("Planilha1").Range("M4").Value
   pvFld.CurrentPage = strFilter
End Sub

Execute a macro para aplicar o filtro.

 

Para limpar o filtro, crie a seguinte macro:

Sub LimparFiltro()
   Dim pTbl As PivotTable
   Set pTbl = ActiveSheet.PivotTables("Tabela dinâmica1")
   pTbl.ClearAllFilters
End Sub

O filtro será então removido.

Em seguida, podemos alterar os critérios de filtro para filtrar em uma linha da tabela dinâmica em vez da página atual.

A digitação da macro a seguir nos permitirá filtrar a linha (observe que o campo dinâmico a ser filtrado agora é o Operação e não o Fornecedor).

Sub FiltroValorLinha()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Operação")
   strFilter = ActiveWorkbook.Sheets("Planilha1").Range("M4").Value
   pvFld.PivotFilters.Add2 xlCaptionEquals, , strFilter
End Sub

Execute a macro para aplicar o filtro.

Uso de Vários Critérios em um Filtro Dinâmico

Podemos acrescentar critérios adicionais ao filtro de valor de linha acima.

Entretanto, como o filtro padrão oculta as linhas que não são necessárias, precisamos fazer um loop pelos critérios e mostrar os que são necessários, enquanto ocultamos os que não são necessários. Isso é feito por meio da criação de uma variável Array e do uso de alguns loops no código.

Sub FiltrarMultiplosItensLinha()
  Dim vArray As Variant
  Dim i As Integer, j As Integer
  Dim pvFld As PivotField
  Set pvFld = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Operação")
  vArray = Range("M4:M5")
  pvFld.ClearAllFilters

  With pvFld
    For i = 1 To pvFld.PivotItems.Count
      j = 1
       Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1
         If pvFld.PivotItems(i).Name = vArray(j, 1) Then
           pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = False
        End If
        j = j + 1
      Loop
    Next i
  End With
End Sub

Criação de um Filtro com Base em uma Variável

Podemos usar os mesmos conceitos para criar filtros com base em variáveis em nosso código, em vez do valor em uma célula. Dessa vez, a variável de filtro (strFilter) é preenchida no próprio código (por exemplo: codificada na macro).

Sub FiltroValorTexto()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("Fornecedor")
   strFilter = "THOMAS S"
   pvFld.CurrentPage = strFilter
End Sub