CODEWITHSUNDEEP
vbaexcel-2010
Andrew Buchanan
Andrew Buchanan

Reputation: 81

Using an array to filter Pivot Field

I'm trying to filter a pivot field to values held within an array using some nested for each loops. The pivot field I'm trying to filter contains accounting periods - the ones I want to keep visible are held in a range of cells on separate sheet - Date Control Sheet. The code I have is as below:

Sub updateMSFPivotCharts()
Dim PivotYear As String, PvtTable As PivotTable, PivotPeriodsRange As Range, DateLastRow As String, PivotPeriods As Variant, ReportPeriodPF As PivotField, YearPF As PivotField, Pi As PivotItem
Dim LboundReportPeriodPF As String, PivotPeriodItem As Variant

PivotYear = Worksheets("Date Control Sheet").Range("A2").Value
DateLastRow = Worksheets("Date Control Sheet").Range("B2").End(xlDown).Row
Set PivotPeriodsRange = Worksheets("Date Control Sheet").Range("B2:B" & DateLastRow)
PivotPeriods = PivotPeriodsRange.Value


'Update Topline Performance Pivot
Call AdjustPivotDataRange("MSF Topline Performance", "1", "A9", "BI", strLastRow)
Set PvtTable = Worksheets("MSF Topline Performance").PivotTables("PivotTable1")
Set YearPF = Worksheets("MSF Topline Performance").PivotTables("PivotTable1").PivotFields("Year")
Set ReportPeriodPF = Worksheets("MSF Topline Performance").PivotTables("PivotTable1").PivotFields("Report Period")

'Filter Pivot to Date Ranges expressed on Date Control Sheet
With PvtTable
    'Update Pivot Table Field Filtering
    YearPF.ClearAllFilters
    ReportPeriodPF.ClearAllFilters
    YearPF.CurrentPage = PivotYear
End With

With ReportPeriodPF
    For Each PivotPeriodItem In PivotPeriods
        For Each Pi In ReportPeriodPF.PivotItems
            If Pi = PivotPeriodItem Then GoTo Skipstep Else Pi.Visible = False
        Next Pi
Skipstep:
    Pi.Visible = True
    Next PivotPeriodItem
End With

The problem I have, is that when I match Pi to PivotPeriodItem and exit out of the loop, Pi starts back at the beginning with PivotPeriodItem moving onto the next Item, hence when Pi is equal to the previous PivotPeriodItem, it then hides it again, when I want it to show.

Does anyone have any suggestion on how I can re-write the code to get by this problem? Many thanks for any help provided in advance.

Andrew

Upvotes: 1

Views: 2218

Answers (1)

dwirony
dwirony

Reputation: 5450

Instead of 

With ReportPeriodPF
    For Each PivotPeriodItem In PivotPeriods
        For Each Pi In ReportPeriodPF.PivotItems
            If Pi = PivotPeriodItem Then GoTo Skipstep Else Pi.Visible = False
        Next Pi
Skipstep:
    Pi.Visible = True
    Next PivotPeriodItem
End With

Replace it with 

With ReportPeriodPF
    For Each Pi In ReportPeriodPF.PivotItems
        If WorksheetFunction.CountIf(PivotPeriodsRange, Pi) > 0 Then
            Pi.Visible = True
        Else
            Pi.Visible = False
        End If
    Next Pi
End With

One loop, just using CountIf to test against a range instead of individual items in the range.

Upvotes: 1

Related Questions