Unfiltering all row data in Pivot Table then filter for specific values

Question

My organization has ~100 divisions. These are rows in my Pivot table. I use VBA to create a new pivot table on a fresh data dump each week. My issue is a few times a year a division deleted or a new one is added in. My current pivot table uses only 15 divisions but my code is effected by all ~100 divisions. (my code below only shows a portion to save space)

I have tried searching the web for hours and using the macro recorder of a better solution than I am currently using.

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Flex Division - Text")
.PivotItems("03").Visible = False
.PivotItems("04").Visible = False
.PivotItems("05").Visible = False
.PivotItems("07").Visible = False
.PivotItems("1A").Visible = False
.PivotItems("1B").Visible = False
.PivotItems("1C").Visible = False
.PivotItems("1F").Visible = False
.PivotItems("1G").Visible = False
.PivotItems("1J").Visible = False
.PivotItems("1K").Visible = False
.PivotItems("(blank)").Visible = False
End With

The code above filters out divisions not in use. I want to do the opposite. I would like to un filter all divisions and then add back in the divisions I use. This will avoid future code adjustments.

What do you mean with "unfilter"? Make them all visible or hide them all? — teylyn, Jan 14 '19 at 21:10
Hide them all. So nothing is visible then add back in the divisions needed. — Matt Lane, Jan 14 '19 at 21:13
@MattLane Check out my answer at https://stackoverflow.com/questions/45718045/pivotfields-multiple-filter/45726720#45726720 that has a fast and efficient code sample to do this. — jeffreyweir, Jan 15 '19 at 09:15

dwirony · Answer 1 · 2019-01-14T21:21:21.257

3

So the first issue here is that you can't have a pivot table with no items in the filter - instead, create an array with all the items you want to keep everytime and check that array in one loop - if the item is in the array, it will make sure it's visible. If it's not in there, it will hide it:

Option Explicit
Sub Test()

Dim pf As PivotField
Dim pt As PivotTable
Dim pi As PivotItem
Dim keeparr As Variant

Set pt = ActiveSheet.PivotTables("PivotTable1")

'List all the item names that you want to keep in here
keeparr = Array("test1", "test2", "test3")

pt.PivotFields("Flex Division - Test").CurrentPage = "(All)"

For Each pf In pt.PageFields
    If pf = "Flex Division - Text" Then
        For Each pi In pf.PivotItems
            If IsError(Application.Match(pi, keeparr, 0)) Then
                If pi.Visible = True Then pi.Visible = False
            Else
                if pi.Visible = False Then pi.Visible = True
            End If
        Next pi
        Exit For
    End If
Next pf

End Sub

For anyone that can avoid looping through all the PageFields and just address it by name, please comment below - I could not figure it out.

edited Jan 14 '19 at 21:21

answered Jan 14 '19 at 21:13

dwirony

5,487
3
21
43

1

Potential issue with a single loop: If the only items currently visible are undesired items, and all occur before any of the desired items in the cycle; then the pivot table will reset to the default "All" filter as soon as the last-visible undesired is set to `False`. – Mistella Jan 14 '19 at 21:18
1

If speed is important, the Visible fields are faster reading than writing, so check if the `.Visible` is what is supposed to be, and only set it if it's incorrect. – Mistella Jan 14 '19 at 21:19
1

@Mistella Good catch - I added a line to unfilter all values before running the loop. – dwirony Jan 14 '19 at 21:21
1

@Mistella I added a logic test vs the `Visible` status of the item. – dwirony Jan 14 '19 at 21:24
Im getting an error code 1004 - Unable to set the current page property of the pivot field class - pt.PivotFields("Flex Division - Text").CurrentPage = "(All)" any ideas? – Matt Lane Jan 14 '19 at 22:00
I changed Test to Text. then I tried to troubleshoot it online for a bit. – Matt Lane Jan 14 '19 at 22:02
1

@MattLane Try switching the error line to `pt.PivotFields("Flex Division - Test").ClearAllFilters` (Although if you have any value filters, you may want to use `.ClearLabelFilters` instead of `.ClearAllFilters`) – Mistella Jan 14 '19 at 22:05
Trying it with "Flex Division -Test" errors out. If it put in "Flex Division -Text" it goes though, but nothing is filtered out. I appreciate all the help. – Matt Lane Jan 14 '19 at 22:24
1

@MattLane IMO just comment out the line - that was there just in case. You could also manually set the pivot table's field to all before running the macro. – dwirony Jan 14 '19 at 22:25
1

The .CurrentPage property only applies to PivotFields in the Page area. It will cause an error for any fields in the Rows or Columns area. – jeffreyweir Jan 15 '19 at 09:10

score 1 · Accepted Answer · answered Jan 15 '19 at 09:20

This code should do what you need. To learn more about filtering PivotTables quickly, check out my blogpost on the subject.

Option Explicit

Sub FilterPivot()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Long
Dim vItem As Variant
Dim vItems As Variant

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("SomeField")

vItems = Array("Item1", "Item2", "Item3")

pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed

With pf

    'At least one item must remain visible in the PivotTable at all times, so make the first
    'item visible, and at the end of the routine, check if it actually  *should* be visible        
    .PivotItems(1).Visible = True

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .PivotItems.Count
        If .PivotItems(i).Visible Then .PivotItems(i).Visible = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vItems
        .PivotItems(vItem).Visible = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the items of interest
    On Error Resume Next
    If InStr(UCase(Join(vItemss, "|")), UCase(.PivotItems(1))) = 0 Then .PivotItems(1).Visible = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Pivot, so I have cleared the filter"
    End If
    On Error GoTo 0

End With

pt.ManualUpdate = False

End Sub

You nailed it. The code worked perfectly with a little tweaking. It found my ~20 divisions but then deleted them out. I deleted out the section of the code "If Err.Number <> 0 then" down to End If". Thanks for the help! — Matt Lane, Jan 28 '19 at 15:05

Unfiltering all row data in Pivot Table then filter for specific values

2 Answers2