VBA: Filtering Rows in Power Pivot Table based on an Array

Question

I have the following code where I am trying to filter my pivot table ROWS based on a certain list of companies. I have already loaded those company names inside the array peerGroup, where I know the length of the array. My If statement correctly identifies which companies in my array that are found in the pivot table but I can't figure out how to hide that value.

I have looked at other threads and the most common answers I found was using .Visible, but when I try to adopt it to my code, I get a Run-time error. "Unable to set Visible property of the PivotItem class".

Dim ct As Integer
Dim i As Integer
Dim PG_RRC_PVT As PivotTable
Dim pi As PivotItem


Set PG_RRC_PVT = Worksheets("Pivot4Charts").PivotTables("PGRRCPVT")

PG_RRC_PVT.PivotFields("[CoTicker].[CompanyMask].[CompanyMask]").ClearAllFilters


For Each pi In PG_RRC_PVT.PivotFields("[CoTicker].[CompanyMask].[CompanyMask]").PivotItems
    For i = 0 To pgCount
         If pi.Name = "[CoTicker].[CompanyMask].&[" & peerGroup(i) & "]" Then
            pi.Visible = False
         End If
    Next i
Next pi

Alex Alex · Accepted Answer · 2017-01-18T21:14:11

After driving myself inside, the fact that I had the dimensions in rows is why .Visible didn't work. To deal with the syntax in VisibleItemList.. you can just adjust your array... See code below

For i = 0 To pgCount - 1
    peerGroup(i) = "[CoTicker].[CompanyMask].&[" & peerGroup(i) & "]"
    MsgBox peerGroup(i)
Next
ActiveSheet.PivotTables("PGRRCPvt").PivotFields("[CoTicker].[CompanyMask].[CompanyMask]").VisibleItemsList = Array(peerGroup)

VBA: Filtering Rows in Power Pivot Table based on an Array

1 Answers