Limpiar valores antiguos de los desplegables en una tabla dinámica de excel

Si has usado alguna vez una tabla dinámica (o Pivot Table) de Excel, es posible que te hayas encontrado alguna vez en la situación que vamos a comentar en este artículo.

¿Cuál es la situación?

Los datos de origen de una tabla dinámica pueden cambiar y los elementos que antes estaban pero ya no no deberían volver a aparecer en los desplegables que se utilizan para filtrar.

Sin embargo, incluso después de pulsar sobre el botón refrescar, los antiguos nombres siguen apareciendo junto con los nuevos.

¿Y cómo se soluciona?

En este artículo proponemos 3 soluciones para limpiar los valores que permanecen en los desplegables y no hay manera de eliminar:

1. Manualmente
2. Programáticamente
3. Modificando las propiedades de la tabla dinámica (sólo es posible a partir de la versión 2007 de Excel).

Limpiar los valores de forma manual.

La forma manual de eliminar los valores consiste en desagrupar todo lo que esté agrupado. Después de esto hay que eliminar el campo de la tabla dinámica (arrastrándolo fuera de la tabla). A continuación pincharemos sobre la opción "Actualizar" de la tabla y, por último, volvemos a introducir el campo en la misma.

Limpiar los valores mediante una llamada a un procedimiento en Visual Basic.

En versiones de Excel 2002 o posteriores, se pueden modificar las propiedades de una tabla dinámica para prevenir que aparezcan elementos eliminados del origen de datos de la siguiente forma:

Sub DeleteOldItemsV1()
  Dim pt As PivotTable
  Dim ws As Worksheet
  Dim pc As PivotCache

  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Next pt
  Next ws

  For Each pc In ActiveWorkbook.PivotCaches
    On Error Resume Next
    pc.Refresh
  Next pc
End Sub   

Si estamos hablando de versiones de Excel anteriores a la 2002, podemos utilizar el siguiente código para eliminar los elementos antiguos:

Sub DeleteOldItemsV2()
  Dim ws As Worksheet
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim pi As PivotItem

  On Error Resume Next
  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      pt.RefreshTable
      pt.ManualUpdate = True
      For Each pf In pt.VisibleFields
        If pf.Name <> "Data" Then
          For Each pi In pf.PivotItems
            If pi.RecordCount = 0 And _
              Not pi.IsCalculated Then
              pi.Delete
            End If
          Next pi
        End If
      Next pf
      pt.ManualUpdate = False
      pt.RefreshTable
    Next pt
  Next ws
End Sub  

Cambiar las opciones de la tabla dinámica (sólo Excel 2007 y versiones posteriores)

En Excel 2007 y en las versiones posteriores ya podemos modificar las opciones de la tabla dinámica, que modificabamos mediante Visual Basic en el punto anterior, pero a través del menú de opciones.

Pinchamos con el botón derecho sobre una celda de la tabla dinámica y hacemos click sobre "Opciones de la tabla dinámica…". En la pestaña "Datos", nos vamos a la sección "Mantener los elementos eliminados del origen de datos" y seleccionamos "Ninguno" en el desplegable. Por último, guardamos los cambios y refrescamos la tabla dinámica pulsando sobre el botón "Actualizar" de la misma.