• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to deselect single slicer item using data model pivot table

morgyt

New Member
I have a pivot table that comes from a data model, and I want to find a macro that will deselect one item named "Headcount". I've tried the two approaches below, but I can't get either to work. Could someone please help point me in the right direction?

Thanks!

First Approach:

Code:
Sub Macro3()

ActiveWorkbook.SlicerCaches("Slicer_Project_Name").ClearManualFilter
        SelectSlicerValue "Slicer_Project_Name", "Headcount", False
End Sub

Second Approach:
Sub Macro1()
Dim a As String
a = "Headcount"
With ActiveWorkbook.SlicerCaches("Slicer_Project_Name")
For I = 1 To .SlicerItems.Count

.SlicerItems(I).Selected = True
If .SlicerItems(I).Value = a Then
.SlicerItems(I).Selected = False
End If
Next
End With
End Sub
 
Last edited by a moderator:

Chihiro

Excel Ninja
Data model based pivot has different syntax.

Use macro recorder to find out the syntax needed.

Ex: To select (i.e. make visible) multiple items.
Code:
ActiveWorkbook.SlicerCaches("Slicer_Name").VisibleSlicerItemsList = Array("[Table/Model Name].[FieldName].&[Value1]","[Table/Model Name].[FieldName].&[Value2]")
Typically what I would do, is using dictionary (using Item Name as key) to hold list, and remove items that you don't want visible (or use If statement to exclude it when building dictionary). Then set Dictionary.Keys as .VisibleSlicerItemsList

NOTE: Item name is "[Table/Model Name].[FieldName].&[Value1]", Item value is Value1 part only. To set VisibleSlicerItemsList, you need array of Item Names and not Item Values.
NOTE2: Another method is to use FILTER() function on array of Item Names and supply that to VisibleSlicerItemsList.

You can find example using dictionary in link below (Post#12)
https://chandoo.org/forum/threads/pivot-date-filter.42595/#post-254203

However, if it's just one item that you don't want... it's far easier to remove it from the data model (i.e. using PowerQuery or some other ETL process).

Alternately, you can construct DAX measure to exclude said item.
 
Last edited:

morgyt

New Member
Thanks much for the pointer...the field is a list of project names, and I'm trying to de-select one called "Headcount". When I try to record a macro I get a Too Many Line Continuations warning. I tried to follow the example you suggested but the macro runs it does the opposite of what I want...it selects the slicer named "Headcount" instead of selecting all slicer names except "Headcount". I also tried substituting 0 for the 1 but get the same result.

Thanks!


Code:
Sub Demo()

Dim slc As SlicerCache

Dim sli As SlicerItem

Dim iDic As Object

    Set slc = ThisWorkbook.SlicerCaches("Slicer_Project_Name")

    Set iDic = CreateObject("Scripting.Dictionary")

  

    For Each sli In slc.SlicerCacheLevels(1).SlicerItems

        With Sheets("Allocation View Data")

            If Item.Value = "Headcount" Then

                iDic(sli.Name) = 1

            End If

        End With

    Next

  

    If iDic.Count = 0 Then

        MsgBox "There is no data in date range specified"

    Else

        slc.VisibleSlicerItemsList = iDic.Keys

    End If

End Sub
 
Last edited by a moderator:

Chihiro

Excel Ninja
Please use code tag to nest your VBA code.
65143

That is expected behavior. As the property name suggest... .VisibleSlicerItemsList will set list of items that should be visible.

So, you need to iterate or cast all items in your slicer and then remove unwanted item(s).

Ex:
Code:
Sub Demo()
Dim slc As SlicerCache
Dim sli As SlicerItem
Dim iDic As Object
Set slc = ThisWorkbook.SlicerCaches("Slicer_Project_Name")
Set iDic = CreateObject("Scripting.Dictionary")

For Each sli In slc.SlicerCacheLevels(1).SlicerItems
    If sli.Value <> "Headcount" Then
        iDic(sli.Name) = 1
    End If
Next

If iDic.Count = 0 Then
    MsgBox "No item selected"
Else
    slc.VisibleSlicerItemsList = iDic.Keys
End If
End Sub
 
Top