Hi,

I want to be able to totally hide the slicers in my sheet. The idea is that the user can select the slicer items they want to see and then hide the slicers afterwards in order to be able to view a clean final report.

I don't want to hide items in the slicer, I actually want to hide the slicer box. I have tried grouping rows and also hiding them and also playing around with the Position settings (i.e. Move but don't size with cells... and others), but the Slicers just stay on the sheet and get pushed down.

I could potentially set the height of the slicer to 0 height in VBA using the below code, but I'm wondering if I'm missing an easy option in Excel.

Dim PT1 As PivotTable
Dim slc1 As Slicer

    Set PT1 = Sheets("ALL").PivotTables("PVT_POS_BRANCH") '(1)
    Set slc1 = PT1.Slicers("POS_CT") '(1)
    slc1.Height = 100

Thanks

Hi UpsideDown55,

You can also set their visible property to false?

Hi UpsideDown55,

You can also set their visible property to false?

Hi,

Just a question, if the slicer is hiden, how to make it show? You want to use a button or something else?

Based on my test, I can do that using the code:

Sub POS_CT_Click()

Dim PT1 As PivotTable

Dim slc1 As Slicer

Set PT1 = Sheets("ALL").PivotTables("PVT_POS_BRANCH") '(1)

Set slc1 = PT1.Slicers("POS_CT") '(1)

slc1.Cut

End Sub

This code will be run when the user click the slicer.

And the slicer will be cut. If you want it show again, just press Ctrl+V.

Hi,

Just a question, if the slicer is hiden, how to make it show? You want to use a button or something else?

Based on my test, I can do that using the code:

Sub POS_CT_Click()

Dim PT1 As PivotTable

Dim slc1 As Slicer

Set PT1 = Sheets("ALL").PivotTables("PVT_POS_BRANCH") '(1)

Set slc1 = PT1.Slicers("POS_CT") '(1)

slc1.Cut

End Sub

This code will be run when the user click the slicer.

And the slicer will be cut. If you want it show again, just press Ctrl+V.

Hi guys,

I was hoping there was a way to do this without VBA, but I was able to put together a function to do this when the user clicks a button:

Sub hideSlicers()
Dim slc1, slc2, slc3, slc4 As Slicer
Dim buttonText As String
Dim slicerHeight As Integer

buttonText = ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text

If buttonText = "Hide Slicers" Then
    slicerHeight = 0
    ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Show Slicers"
    ActiveSheet.Rows("1:13").Hidden = True
Else
    slicerHeight = 180
    ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Hide Slicers"
    ActiveSheet.Rows("1:13").Hidden = False
End If

Set slc1 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_CT")
Set slc2 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_REG")
Set slc3 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_CT")
Set slc4 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_REG")

slc1.Height = slicerHeight
slc2.Height = slicerHeight
slc3.Height = slicerHeight
slc4.Height = slicerHeight

Range("A1").Select

End Sub

• Marked as answer by UpsideDown55 Wednesday, November 14, 2012 10:12 AM

Hi guys,

I was hoping there was a way to do this without VBA, but I was able to put together a function to do this when the user clicks a button:

Sub hideSlicers()
Dim slc1, slc2, slc3, slc4 As Slicer
Dim buttonText As String
Dim slicerHeight As Integer

buttonText = ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text

If buttonText = "Hide Slicers" Then
    slicerHeight = 0
    ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Show Slicers"
    ActiveSheet.Rows("1:13").Hidden = True
Else
    slicerHeight = 180
    ActiveSheet.Shapes.Range(Array("Button 1")).TextFrame.Characters.Text = "Hide Slicers"
    ActiveSheet.Rows("1:13").Hidden = False
End If

Set slc1 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_CT")
Set slc2 = ActiveSheet.PivotTables("PVT_POS_BRANCH").Slicers("POS_REG")
Set slc3 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_CT")
Set slc4 = ActiveSheet.PivotTables("PVT_FULL_BAND").Slicers("FULL_REG")

slc1.Height = slicerHeight
slc2.Height = slicerHeight
slc3.Height = slicerHeight
slc4.Height = slicerHeight

Range("A1").Select

End Sub

• Marked as answer by UpsideDown55 Wednesday, November 14, 2012 10:12 AM

(It's been a while since this question was posted however it is worth responding just in case someone else has the same question down the road.)

 There is a much easier way that doesn't involve VBA. (I'm using Excel 2010)

Go to the Home tab, Editing group, Find & Select, and click on Selection Pane. Click on the eye icon beside the slicer name.

HTH - JoAnn

(It's been a while since this question was posted however it is worth responding just in case someone else has the same question down the road.)

 There is a much easier way that doesn't involve VBA. (I'm using Excel 2010)

Go to the Home tab, Editing group, Find & Select, and click on Selection Pane. Click on the eye icon beside the slicer name.

HTH - JoAnn

I Agree with JoAnn,

This is easier and effective !  Thanks for sharing JoAnn :)

Cheers

- KG

I Agree with JoAnn,

This is easier and effective !  Thanks for sharing JoAnn :)

Cheers

- KG

Hi JoAnn,

Thanks so much for that. Based on your instructions, I recorded a macro of the same and got the following code. This would be useful if anyone wants to have a button the user can click on to hide all slicers:

ActiveSheet.Shapes.Range(Array("POS_CT")).Visible = msoFalse  'TO HIDE THE SLICER
ActiveSheet.Shapes.Range(Array("POS_CT")).Visible = msoTrue   'TO SHOW THE SLICER

• Proposed as answer by Ionut Rovin Popescu Thursday, September 26, 2013 11:08 AM
• Unproposed as answer by Ionut Rovin Popescu Thursday, September 26, 2013 11:08 AM

Hi JoAnn,

Thanks so much for that. Based on your instructions, I recorded a macro of the same and got the following code. This would be useful if anyone wants to have a button the user can click on to hide all slicers:

ActiveSheet.Shapes.Range(Array("POS_CT")).Visible = msoFalse  'TO HIDE THE SLICER
ActiveSheet.Shapes.Range(Array("POS_CT")).Visible = msoTrue   'TO SHOW THE SLICER

• Proposed as answer by Ionut Rovin Popescu Thursday, September 26, 2013 11:08 AM
• Unproposed as answer by Ionut Rovin Popescu Thursday, September 26, 2013 11:08 AM

Hello,

I did the following :

1) I have grouped 10 columns (Data - Group)

2) Moved the slicers into the grouped columns area

3) Selected all my slicers, went to Options and selected Group.

4) added a VBA to expand or hide the grouped columns (the slicers disappear with the columns)

Hope this works for you also.

Br,

Ionut

I guess I'm old school. I would rather teach someone how to use the Selection Pane than fuss with a macro. Plus many of our files go on shared drives and that means adding more folders to the trusted locations.

(I won't mention how no one else at work uses slicers other than me.)

JoAnn

I guess I'm old school. I would rather teach someone how to use the Selection Pane than fuss with a macro. Plus many of our files go on shared drives and that means adding more folders to the trusted locations.

(I won't mention how no one else at work uses slicers other than me.)

JoAnn

Helpfull, Thank you JoAnn !

Regards,

ShpendG

Helpfull, Thank you JoAnn !

Regards,

ShpendG

Thank You so much for providing the most easiest and effective method.

Thank You so much for providing the most easiest and effective method.