使用VBA清除切片器时如何排除两个指定切片器的技术咨询

Got it, let's tweak your code so you can preserve selections for two slicers while clearing all others. The problem with your current snippet is it only checks for one exclusion condition—we can expand this to handle multiple slicers you want to skip, and make it easy to add more later if needed.

Modified Code #

Here's an updated version that lets you specify multiple slicers to keep:

Sub Clearslcr()
    Dim Slcr As SlicerCache
    Dim SL As Slicer
    ' Define the list of slicers (names or keywords) we want to KEEP
    Dim keepSlicers As Variant
    keepSlicers = Array("Measure", "YourSecondSlicerName") ' Replace the second value with your actual slicer name/keyword
    
    For Each Slcr In ActiveWorkbook.SlicerCaches
        For Each SL In Slcr.Slicers
            ' Only target slicers on the active sheet (matches your original logic)
            If SL.Parent.Name = ActiveSheet.Name Then
                ' Check if the slicer is NOT in our keep list
                If Not IsInArray(SL.Name, keepSlicers) Then
                    ' Clear the slicer's manual selection
                    Slcr.ClearManualFilter
                End If
            End If
        Next SL
    Next Slcr
End Sub

' Helper function to check if a slicer name matches any entry in our keep list
Function IsInArray(valToFind As String, arr As Variant) As Boolean
    Dim element As Variant
    For Each element In arr
        ' Use this line for PARTIAL matches (e.g., any slicer with "Measure" in its name)
        If InStr(valToFind, element) > 0 Then
        ' Uncomment below and comment above for EXACT name matches
        'If valToFind = element Then
            IsInArray = True
            Exit Function
        End If
    Next element
    IsInArray = False
End Function

Key Changes Explained #

• Keep List Array: The keepSlicers array lets you list all slicers you want to preserve. Just replace "YourSecondSlicerName" with the actual name (or keyword) of your second slicer. You can even add more entries here later if needed.
• Helper Function: IsInArray makes the code cleaner by handling the check for whether a slicer should be kept. It supports two modes:
  • Partial matches (default): Use this if your slicer names include a keyword (like all "Measure" related slicers).
  • Exact matches: Uncomment the If valToFind = element Then line if you need to target slicers with precise names.
• Consistent Logic: We kept your original check for slicers on the active sheet, so it only affects slicers in the sheet you're working on.

Quick Notes #

• Double-check the spelling of your slicer names—Excel is case-insensitive here, but typos will break the logic.
• If you need to preserve more than two slicers later, just add another entry to the keepSlicers array (e.g., keepSlicers = Array("Measure", "Category", "Region")).

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