VBA Pivot Filtering without Field use

[chirayu]

Hi Guys,

Related to thread:
http://forum.chandoo.org/threads/vba-slicer-userform-version.30524/

I was thinking of expanding the functionality on this by doing something I saw once in someone else's VBA Slicer. Forget whose but anyways, what I mean by the title is that - without adding a PivotField to either Rows/Columns/Values/Filters how can I apply a filter to that field & make that change visible in the Pivot?

You see I noticed that my macro does already apply the filter to the Field - even if it is not being used in either of those. But the change is not visible in the pivot itself till you move that field to one of those categories in the pivot configuration.

So I was wondering if anyone had any clue as to how I could do this.

Also just as an FYI - I have recently fixed a bug in the macro where hitting Unfilter without choosing a Pivot/Field from the dropdowns throws an error. Also I have added an extra button that Unfilters all fields in the entire pivot; and I have added an extra bit that will highlight the used filters in the selected field of the dropdown, so you don't have to figure it out yourself when changing fields. But won't be releasing a new version till I'm able to figure out how to do the above.

Also hit another hurdle. How do I check which items are visible in a report filter? as .PivotItems doesn't work for .xls files

 

[Chihiro]

To do this without adding "Slicer" to the sheet. First you need to create SlicerCache in VBA. Using your workbook as example. Something like...

Dim slc As SlicerCache
Set slc = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), _
        "Container Size")

You'll probably want to check if SlicerCache is already present in the workbook (since it will throw error if it is and you try to add it).

Edit: FYI - SlicerCache name of the cache added using above method is going to be "Slicer_Container_Size"

Then you can do something like below to filter.

Sub Test()
Dim slc As SlicerCache
Dim sli As SlicerItem
Set slc = ActiveWorkbook.SlicerCaches("Slicer_Container_Size")
For Each sli In slc.SlicerItems
If Not sli.Name = "GP40'" Then
    sli.Selected = False
End If
Next sli

End Sub

 

[chirayu]

@Chihiro thanks for the code but I can't do anything with it. As the original thread states - my VBA slicer is a UserForm. It manipulates the pivot. It isn't a slicer manipulation.

 

[Chihiro]

Yep I understand, you don't actually need Slicer object. Just the cache.

 

[Chihiro]

If you wanted to add actual Slicer object and not just the cache, then you need additional process like below.

Sub demoSlicersAdd()
Dim slc As SlicerCache
Dim slobj As Slicer
Set slc = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), _
        "Container Size")
Set slobj = slc.Slicers.Add(ActiveSheet, , "Container Size", "Container Size" _
        , 194.25, 452.25, 144, 198.75)
End Sub

My first post demonstrates way to work around inability of pivottable to filter for fields that are not present. By adding background SlicerCache which will not be visible.

 

[Chihiro]

Other way is to add PivotField to xlPageField. However, this method will shift layout of the sheet. Which may not be desirable in some circumstances.

Sub Demo()
Dim pvF As PivotField
Dim pvI As PivotItem
Set pvF = ActiveSheet.PivotTables("PivotTable1").PivotFields("Container Size")

    With pvF
        .Orientation = xlPageField
        .Position = 1
    End With
    fName = Join(Array(pvF.PivotItems(1), pvF.PivotItems(2)), ",")
    For Each pvI In pvF.PivotItems
        If Not InStr(1, fName, pvI.Name) > 0 Then
            pvI.Visible = False
        Else
            pvI.Visible = True
        End If
    Next pvI
End Sub

 

[chirayu]

Thanks @Chihiro

Yeah I already found out how to do the xlPageField to xlRowField & then run the existing code but noticed that when you turn it back to xlPageField then the report filters will be in different positions. As for the SlicerCache - I'm using Excel 2007. Slicers are from Excel 2010 onwards so that code wouldn't work but thanks.

 

[chirayu]

@Luke M @Hui @bobhc do you guys have any links/ ideas on how I can filter the table without active fields in the pivot?

Btw @Chihiro I resolved the issue with the report filter moving around when you do the xlpagefield to xlrowfield. I just save the ".position" as a variable & implement it back in when converting back to xlpagefield.

 

[Luke M]

I'm not sure you can. As you mentioned before, in 2010+ you could make use of the SlicerCache to store the filter settings, but we don't want to use that in this case. With 2007 and earlier, even if you had a visible filter, if you removed that field, the "filter" is lost.

 

[chirayu]

Thanks @Luke M . Wondering if I'm going mad as I'm sure I saw that functionality in Tushar's version, the one @jeffreyweir showed in the original "VBA Slicer (basic code)" thread. Could just be imagining things. Anyway. I'm gonna release the latest iteration. I've tried to debug it as much as I can and apart from the features I added earlier (stated in 1st thread post), I added a re-slice button. Will explain in detail in main thread.