Using an array to filter Pivot Field

Question

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

Well I'd start by recommending that you don't use a GoTo to do this, it's generally a bad practice. Instead of looping through each item in PivotPeriods, why not use WorsheetFunction.CountIf and test to make sure it's not in the range? — dwirony
– dwirony, Commented Feb 27, 2018 at 16:09

dwirony · Accepted Answer · 2018-02-27 16:12:05Z

1

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.

answered Feb 27, 2018 at 16:12

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using an array to filter Pivot Field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related