Slicer Object - adjust size based on content

[288enzo]

Greetings, is it possible to make a slicer object size based on the number of selections available?

I have this -

Sub SLICER()
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table1"), "Area").Slicers.add ActiveSheet, , "Area", "Area", 1, 600, 150, 150
    With ActiveWorkbook.SlicerCaches("Slicer_Area").Slicers("Area")
        .Caption = "Area"
        .DisplayHeader = True
        .SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
        .SlicerCache.SortItems = xlSlicerSortAscending
        .SlicerCache.SortUsingCustomLists = True
    End With
End Sub

I was hoping I could set the size to automatically adjust based on the content, like "Area", "Area", 1, 600, , , - basically not declaring values for the width and height.

 

[Domenic]

Actually, since the width and height parameters are optional, you can simply omit them like this . . .

.Slicers.Add ActiveSheet, , "Area", "Area", 1, 600

Hope this helps!

 

[Domenic]

By the way, your macro can be re-written as follows . . .

Sub SLICER()

    Dim sc As SlicerCache
    Set sc = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table1"), "Area")
    
    Dim sl As SLICER
    Set sl = sc.Slicers.Add(ActiveSheet, , "Area", "Area", 1, 600)
    
    With sl
        .Caption = "Area"
        .DisplayHeader = True
        .SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
        .SlicerCache.SortItems = xlSlicerSortAscending
        .SlicerCache.SortUsingCustomLists = True
    End With
    
End Sub

Hope this helps!

 

[288enzo]

Actually, since the width and height parameters are optional, you can simply omit them like this . . .

.Slicers.Add ActiveSheet, , "Area", "Area", 1, 600

Hope this helps!

Thank you, when I omit the height I was hoping by default the height would show all options without the slider on the right.

 

[Domenic]

Oh I see, in that case, try something like this (you might have to play with it a bit to find the right settings) . . .

.Height = 30 + ((.RowHeight + 2) * .SlicerCache.SlicerItems.Count)

If you are going to the code I suggested earlier . . .

Sub SLICER()

    Dim sc As SlicerCache
    Set sc = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table1"), "Area")
    
    Dim sl As SLICER
    Set sl = sc.Slicers.Add(ActiveSheet, , "Area", "Area", 1, 600)
    
    With sl
        .Caption = "Area"
        .DisplayHeader = True
        .SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
        .SlicerCache.SortItems = xlSlicerSortAscending
        .SlicerCache.SortUsingCustomLists = True
        .Height = 30 + ((.RowHeight + 2) * .SlicerCache.SlicerItems.Count)
    End With
    
End Sub

Hope this helps!

 

[288enzo]

Oh I see, in that case, try something like this (you might have to play with it a bit to find the right settings) . . .

.Height = 30 + ((.RowHeight + 2) * .SlicerCache.SlicerItems.Count)

If you are going to the code I suggested earlier . . .

Sub SLICER()

    Dim sc As SlicerCache
    Set sc = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table1"), "Area")
   
    Dim sl As SLICER
    Set sl = sc.Slicers.Add(ActiveSheet, , "Area", "Area", 1, 600)
   
    With sl
        .Caption = "Area"
        .DisplayHeader = True
        .SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
        .SlicerCache.SortItems = xlSlicerSortAscending
        .SlicerCache.SortUsingCustomLists = True
        .Height = 30 + ((.RowHeight + 2) * .SlicerCache.SlicerItems.Count)
    End With
   
End Sub

Hope this helps!

This worked, I did have to change .RowHeight + 2 to +3, but it worked. Thank you.

Can you help explain what the last line is doing? .Height = 30 + ((.RowHeight + 2) * .SlicerCache.SlicerItems.Count)

 

[Domenic]

This worked, I did have to change .RowHeight + 2 to +3, but it worked. Thank you.

That's great, glad it worked.

Can you help explain what the last line is doing? .Height = 30 + ((.RowHeight + 2) * .SlicerCache.SlicerItems.Count)

• 30 is to allow extra space for the header
• ((.RowHeight + 2) is the height of a slicer item, plus 2 for some padding on either side of it
• .SlicerCache.SlicerItems.Count is a count of the number of slicer items in the slicer

Cheers!

 

[288enzo]

That's great, glad it worked.

• 30 is to allow extra space for the header
• ((.RowHeight + 2) is the height of a slicer item, plus 2 for some padding on either side of it
• .SlicerCache.SlicerItems.Count is a count of the number of slicer items in the slicer

Cheers!

Genius!

Many thanks.

 

[Domenic]

You're very welcome, cheers!