VBA取消切片器全部选项（保留1个）代码运行异常问题

Got it, let's sort out this slicer selection issue. The goal is clear: when a numbered item (like #12) is selected, we want to auto-select all items from #1 up to that selected one, while leaving everything below (plus the empty penultimate item and final special entry) unselected. And we need to make sure this plays nice with MultiSelect=True.

Here's a revised VBA code that handles all these edge cases:

Sub SelectSliceItemsUpToCurrent()
    Dim slcrCache As SlicerCache
    Dim slcItem As SlicerItem
    Dim currentIndex As Integer
    Dim targetIndex As Integer
    Dim foundSelected As Boolean
    
    ' Replace with your actual slicer cache name (check Slicer Tools > Options)
    Set slcrCache = ThisWorkbook.SlicerCaches("Slicer_YourFieldName")
    
    ' First, locate the index of the currently selected numbered item
    currentIndex = 0
    For Each slcItem In slcrCache.SlicerItems
        currentIndex = currentIndex + 1
        ' Skip empty penultimate item and final "..." entry
        If slcItem.Name <> "" And slcItem.Name <> "..." Then
            If slcItem.Selected Then
                targetIndex = currentIndex
                foundSelected = True
                Exit For
            End If
        End If
    Next slcItem
    
    ' Only proceed if we found a valid selected item
    If foundSelected Then
        ' Clear all existing selections to avoid conflicts
        slcrCache.ClearManualFilter
        
        ' Now select items from #1 up to the target index
        currentIndex = 0
        For Each slcItem In slcrCache.SlicerItems
            currentIndex = currentIndex + 1
            ' Check if this is one of our numbered #1-#22 items
            If slcItem.Name Like "#[0-9]*" Or IsNumeric(Replace(slcItem.Name, "#", "")) Then
                slcItem.Selected = (currentIndex <= targetIndex)
            Else
                ' Leave empty and special final items unselected
                slcItem.Selected = False
            End If
        Next slcItem
    End If
End Sub

Key Details & Fixes: #

• Target Index Detection: We first loop through slicer items to find the index of the selected numbered entry, explicitly skipping the empty penultimate item and final "..." so they don't throw off our index count.
• Clean Slate Reset: Using ClearManualFilter ensures we start with no selections, which prevents old selections from interfering with the new range we want to set.
• Smart Item Filtering: The condition slcItem.Name Like "#[0-9]*" Or IsNumeric(Replace(slcItem.Name, "#", "")) ensures we only interact with the valid #1-#22 items. Empty and special entries are forced to stay unselected.
• MultiSelect Compatibility: Since we're manually toggling each item's Selected property, this works seamlessly with MultiSelect=True without overriding the slicer's multi-select setting.

Quick Notes: #

• Swap "Slicer_YourFieldName" with your actual slicer cache name (you can find this in the Slicer Tools > Options tab under "Slicer Cache Name").
• If the final special item has a different label than "...", update the skip condition in the first loop to match its exact name.
• This assumes only one numbered item is selected initially (since the goal is to select up to that single selected entry).

内容的提问来源于stack exchange，提问作者urdearboy