Handling Non-Occurring Filter Values in PivotTable Filter using VBA

To filter non-occurring values in a PivotTable using VBA, follow these steps:

1. First, ensure that your PivotTable has data from three categories: We, Rio, and Groen. We assume that you have copied the PivotTable from one worksheet to three separate worksheets named We, Rio, and Groen.

2. Press Alt + F11 to open the VBA editor.

3. In the VBA editor, click Insert > Module to create a new module.

4. Paste the following code into the module:

Sub FilterPivotTable()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim ws As Worksheet

    ' Change the source worksheet name here
    Set ws = ThisWorkbook.Sheets("We")

    ' Set the PivotTable and PivotField
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Category")

    ' Loop through all PivotItems in the PivotField
    For Each pi In pf.PivotItems
        ' Check if the PivotItem is not selected (i.e., it's not in the PivotTable)
        If pi.Visible = False Then
            ' Add the PivotItem to the PivotTable
            pi.Visible = True
            pt.RefreshTable
        End If
    Next pi

    ' Loop through all PivotFields and repeat the process
    For Each pf In pt.PivotFields
        Call FilterPivotTable
    Next pf

End Sub

5. Change the source worksheet name ("We" in the code) to the name of the worksheet containing your PivotTable.

6. Save the VBA module and close the VBA editor.

7. Run the macro by pressing Alt + F8, selecting the macro, and clicking Run.

This code will loop through all PivotItems in the "Category" PivotField and check if they are not visible (i.e., not in the PivotTable). If that's the case, it will make the PivotItem visible and refresh the PivotTable. Then, it will repeat the process for all PivotFields in the PivotTable.

Remember that this code assumes that your PivotTable has a "Category" PivotField. If you have a different PivotField name, you'll need to adjust the code accordingly.

Here's the HTML output of the article:

Filter Non-Occurring Values in a PivotTable using VBA

PivotTables can contain data from three categories, which I'll call We, Rio, and Groen. There are three worksheets, each containing a copied PivotTable.

To filter non-occurring values in a PivotTable using VBA, follow these steps:

1. Open the VBA editor by pressing Alt + F11.
2. Create a new module by clicking Insert > Module.
3. Paste the following code into the module:

Sub FilterPivotTable()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim ws As Worksheet

    ' Change the source worksheet name here
    Set ws = ThisWorkbook.Sheets("We")

    ' Set the PivotTable and PivotField
    Set pt = ws.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Category")

    ' Loop through all PivotItems in the PivotField
    For Each pi In pf.PivotItems
        ' Check if the PivotItem is not selected (i.e., it's not in the PivotTable)
        If pi.Visible = False Then
            ' Add the PivotItem to the PivotTable
            pi.Visible = True
            pt.RefreshTable
        End If
    Next pi

    ' Loop through all PivotFields and repeat the process
    For Each pf In pt.PivotFields
        Call FilterPivotTable
    Next pf

End Sub

4. Change the source worksheet name ("We" in the code) to the name of the worksheet containing your PivotTable.
5. Save the VBA module and close the VBA editor.
6. Run the macro by pressing Alt + F8, selecting the macro, and clicking Run.

This code will loop through all PivotItems in the "Category" PivotField and check if they are not visible (i.e., not in the PivotTable). If that's the case, it will make the PivotItem visible and refresh the PivotTable. Then, it will repeat the process for all PivotFields in the PivotTable.

Remember that this code assumes that your PivotTable has a "Category" PivotField. If you have a different PivotField name, you'll need to adjust the code accordingly.

References

• VBA PivotTable Object Model
• PivotItem Object (Excel)
• PivotField Object (Excel)
• PivotTable Object (Excel)
• Worksheet Object (Excel)