VBA – Filtro de Tabla Dinámica

Este tutorial demostrará cómo utilizar el filtro de tabla dinámica en VBA.

Las tablas dinámicas  son una herramienta de datos excepcionalmente poderosa de Excel. Las tablas dinámicas nos permiten analizar e interpretar grandes cantidades de datos agrupando y resumiendo campos y filas. Podemos aplicar filtros a nuestras tablas dinámicas para poder ver rápidamente los datos que son relevantes para nosotros.

En primer lugar, necesitamos crear una tabla dinámica para nuestros datos. (Haga clic aquí para nuestra guía de tablas dinámicas VBA)

Creación de un filtro basado en el valor de una celda

En este ejemplo vamos a filtrar una tabla dinámica basada en un valor de una Celda. podemos filtrar en el campo Descripción o en un campo de Fila (por ejemplo en el campo Proveedor de arriba o en el campo Operadora que está en la columna Etiquetas de Fila).

En una celda vacía a la derecha de la tabla dinámica, cree una celda para contener el filtro y, a continuación, escriba los datos en la celda sobre la que desea filtrar la tabla dinámica.

Cree la siguiente macro VBA:

Sub FiltrarTablaDinamica()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("TablaDinámica1").PivotFields("Proveedor")
   strFilter = ActiveWorkbook.Sheets("Hoja1").Range("L4").Value
   pvFld.CurrentPage = strFilter
End Sub

Ejecute la macro para aplicar el filtro.

Para borrar el filtro, cree la siguiente macro:

Sub BorrarFiltro()
   Dim pTbl As PivotTable
   Set pTbl = ActiveSheet.PivotTables("TablaDinámica1")
   pTbl.ClearAllFilters
End Sub

El filtro será eliminado. A continuación, podemos modificar los criterios de filtrado para filtrar en una fila de la tabla dinámica en lugar de la página actual.

Escribiendo la siguiente macro nos permitirá filtrar en la Fila (nótese que el Campo a filtrar es ahora el Operadora en lugar del Proveedor).

Sub FiltrarValorFila()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("TablaDinámica1").PivotFields("Operadora")
   strFilter = ActiveWorkbook.Sheets("Hoja1").Range("L4").Value
   pvFld.PivotFilters.Add2 xlCaptionEquals, , strFilter
End Sub

Ejecute la macro para aplicar el filtro.

 

Uso de múltiples criterios en un filtro dinámico

Podemos ampliar el filtro de valor de fila anterior añadiendo criterios adicionales.

Sin embargo, como el filtro estándar oculta las filas que no se requieren, necesitamos hacer un bucle a través de los criterios y mostrar los que se requieren, mientras se ocultan los que no se requieren. Esto se hace creando una variable Array y utilizando un par de bucles en el código.

Sub FiltroPorVariosElementosdeFila()
  Dim vArray As Variant
  Dim i As Integer, j As Integer
  Dim pvFld As PivotField
  Set pvFld = ActiveSheet.PivotTables("TablaDinámica1").PivotFields("Operadora")
  vArray = Range("L4:L5")
  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

Crear un filtro basado en una variable

Podemos utilizar los mismos conceptos para crear filtros basados en variables en nuestro código en lugar de en el valor de una celda. Esta vez, la variable del filtro (strFilter) se rellena en el propio código (por ejemplo, codificado en la macro).

Sub FiltroValorTexto()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("TablaDinámica1").PivotFields("Proveedor")
   strFilter = "Lider"
   pvFld.CurrentPage = strFilter
End Sub