Pivot table automated filter

frankT68

New Member
Joined
Jul 30, 2014
Messages
13
Hi!

I'm using this code to filter a pivot table based on ItemId's in named range "Include_this":
VBA Code:
Sub Filter_Pivot()

    Dim PI As PivotItem
    Dim i As Integer
 

    With Worksheets("Data_chart").PivotTables("Data_ch1").PivotFields("ItemID")
        .ClearAllFilters 'počisti filtre
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0

        Next PI
    End With
End Sub

So if an ItemID from pivot table is found in named range"Include_this" then it visible in a pivot table, otherwise it is not visible.

Today when I have created a new excel file with a new pivot table and the same "Include_this" range as before, I got an error when running the code. The code runs as usual, but then, when it comes to a certain ItemID, it stops. It shows

Error '1004': Unable to set the Visible property of the PivotItem class

Debugging shows there is something wrong with this line
VBA Code:
PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0

I have no idea what is wrong. As I've said, I'm using this code a lot and haven't had any trouble so far. Does somebody know what could be wrong?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
That's probably because your pivot cache is retaining items that you've deleted from the data source. Try setting the MissingItemsLimit property of the PivotCache object to xlMissingItemsNone so that it doesn't retain any old items, and then refresh the pivot cache before proceeding with your code...

VBA Code:
Sub Filter_Pivot()

    Dim PI As PivotItem
    Dim i As Integer

    With Worksheets("Data_chart").PivotTables("Data_ch1")
        With .PivotCache
            .MissingItemsLimit = xlMissingItemsNone
            .Refresh
        End With
        With .PivotFields("ItemID")
            .ClearAllFilters 'pocisti filtre
            For Each PI In .PivotItems
                PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0
            Next PI
        End With
    End With
   
End Sub
 
Solution

frankT68

New Member
Joined
Jul 30, 2014
Messages
13
That's probably because your pivot cache is retaining items that you've deleted from the data source. Try setting the MissingItemsLimit property of the PivotCache object to xlMissingItemsNone so that it doesn't retain any old items, and then refresh the pivot cache before proceeding with your code...

VBA Code:
Sub Filter_Pivot()

    Dim PI As PivotItem
    Dim i As Integer

    With Worksheets("Data_chart").PivotTables("Data_ch1")
        With .PivotCache
            .MissingItemsLimit = xlMissingItemsNone
            .Refresh
        End With
        With .PivotFields("ItemID")
            .ClearAllFilters 'pocisti filtre
            For Each PI In .PivotItems
                PI.Visible = WorksheetFunction.CountIf(Range("Include_this"), PI.Name) > 0
            Next PI
        End With
    End With
  
End Sub
Thank you for your advice. After adding the MissingItemsLimit property the code runs just fine.

But I wonder if this problem could also be related to a small amount of RAM? My office computer runs windows 10 with only 4 GB of RAM, which is usually more than 90% occupied.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
You're very welcome, glad I could help.

No, I don't think so. With only 4 GB of RAM, though, you most likely find that your computer runs excruciating slow, especially when you have multiple programs opened.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,471
Messages
5,596,345
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top