How to Add and Delete Table Slicers in Excel VBA | Automating Dynamic Filtering UIs

Slicers are a convenient feature that allows you to visually filter Excel tables and PivotTables.

By using VBA, you can automatically add, delete, and rearrange slicers.

This article explains the steps to set up and remove slicers using VBA with specific code examples.

Common Use Cases

• Automatically inserting slicers into monthly reports that have different layouts.
• Batch generating reports containing slicers via VBA.
• Automatically deleting or initializing slicers after a user has finished using them.

VBA Code to Add a Slicer

The following code creates a slicer for the “Representative” field in a specific table.

Sub AddSlicerToTable()

    Dim salesTable As ListObject
    Dim displayArea As Range
    Dim slicerCache As SlicerCache
    Dim slicerObj As Slicer

    ' Specify the table and the display position
    Set salesTable = ActiveSheet.ListObjects("SalesList")
    Set displayArea = Range("J2:L10")

    ' Create the Slicer Cache
    Set slicerCache = ThisWorkbook.SlicerCaches.Add2(salesTable, "Representative")

    ' Create the Slicer Object
    Set slicerObj = slicerCache.Slicers.Add( _
        ActiveSheet, , "Representative", _
        Top:=displayArea.Top, Left:=displayArea.Left, _
        Width:=displayArea.Width, Height:=displayArea.Height)

    ' Select the first cell of the table (Optional)
    salesTable.Range.Cells(1).Select

End Sub

VBA Code to Delete All Slicers

The following code removes all slicers existing in the workbook.

Sub DeleteAllSlicers()

    Dim sCache As SlicerCache

    For Each sCache In ThisWorkbook.SlicerCaches
        With sCache
            .ClearAllFilters
            .Delete
        End With
    Next

End Sub

Supplement: Automating Multiple Slicers

If you need to set up slicers for multiple fields, you can easily implement this using a loop.

Dim fieldName As Variant
' Array contains field names like "Representative", "Region", "Category"
For Each fieldName In Array("Representative", "Region", "Category")
    ThisWorkbook.SlicerCaches.Add2(salesTable, fieldName). _
        Slicers.Add ActiveSheet
Next

Key Points and Notes

Summary

By adding and removing slicers with Excel VBA, you can flexibly control the interface for automated reports and data visualization.

This technique is very useful for data analysis and improving usability, as it allows you to easily provide users with filters for specific fields.