Draaitabellen filteren met vba (met voorbeelden)

U kunt de volgende methoden gebruiken om draaitabellen in Excel te filteren met VBA:

Methode 1: Filter de draaitabel op basis van een waarde

 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

Deze specifieke macro filtert de draaitabel met de naam Draaitabel1 , zodat alleen die rijen worden weergegeven waarin de waarde in de kolom Positie van de draaitabel gelijk is aan de waarde in cel J2 van Blad1 .

Methode 2: Filter de draaitabel op basis van meerdere waarden

 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

Deze specifieke macro filtert de draaitabel met de naam Draaitabel1 om alleen die rijen weer te geven waarin de waarde in de kolom Positie van de draaitabel gelijk is aan een van de waarden in het celbereik J2:J3 .

Methode 3: draaitabelfilters verwijderen

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

Deze specifieke macro wist alle filters uit de draaitabel genaamd PivotTable1 .

De volgende voorbeelden laten zien hoe u elk van deze methoden in de praktijk kunt gebruiken.

Voorbeeld 1: Filter de draaitabel op basis van een waarde

Laten we zeggen dat we een draaitabel hebben gemaakt op basis van een gegevensset in Excel om de punten samen te vatten die zijn gescoord door basketbalspelers uit verschillende teams en posities:

Stel dat we de draaitabel willen filteren, zodat alleen rijen worden weergegeven waarvan de waarde in de kolom Positie Bewaker is.

We kunnen hiervoor de volgende macro maken:

 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

Wanneer we deze macro uitvoeren, wordt de draaitabel automatisch gefilterd om alleen rijen weer te geven waarvan de waarde in de kolom Positie Bewaker is:

De draaitabel is gefilterd om alleen rijen weer te geven waarvan de waarde in de kolom Positie Bewaker is.

Voorbeeld 2: Filter een draaitabel op basis van meerdere waarden

Stel dat we in plaats daarvan de draaitabel willen filteren om alleen rijen weer te geven waarvan de waarde in de kolom Positie Bewaker of Midden is.

We kunnen hiervoor de volgende macro maken:

 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

Wanneer we deze macro uitvoeren, wordt de draaitabel automatisch gefilterd om alleen rijen weer te geven waarvan de waarde in de kolom Positie Bewaker of Midden is:

De draaitabel is gefilterd om alleen rijen weer te geven waarvan de waarde in de kolom Positie Bewaker of Midden is.

Aanvullende bronnen

In de volgende tutorials wordt uitgelegd hoe u andere veelvoorkomende taken in VBA kunt uitvoeren:

VBA: draaitabellen vernieuwen
VBA: hoe dubbele waarden te verwijderen
VBA: het aantal rijen binnen bereik tellen