Add Slicers To Table Headers Excel VBA Macro

Slicers are a cool feature initially used only with pivots that Microsoft Excel 2013 and following versions made available for data tables too. In this post we see how to add slicers in Excel using VBA macros. More specifically, the macro code below adds the slicers on top of table headers to allow filtering the table using the slicer instead of the drop-down filter option. It adds as many slicers as columns in the Excel data set that we want to filter.

Macro code:

 
  Sub AddSlicersToTable()
      Dim rng As Range, rngtable As ListObject, col As Integer, colname As String
 
      Set rng = ActiveSheet.UsedRange
 
      'convert range to table
      If ActiveSheet.ListObjects.Count = 0 Then ActiveSheet.ListObjects.Add xlSrcRange, rng
      Set rngtable = ActiveSheet.ListObjects(1)
 
      'add slicers
      For col = 1 To rng.Columns.Count
          With Cells(1, col)
              colname = .Value
              .RowHeight = 90
              .ColumnWidth = 20
   
              ActiveWorkbook.SlicerCaches.Add2(rngtable, colname). _
              Slicers.Add ActiveSheet, , colname, colname, .Top, .Left, .Width, .Height
          End With
      Next col
  End Sub
 

Macro explained:

• First, we declare a Range object variable to store the range with data (rng). We need to convert that range into a table and store that in another variable declared as a ListObject (rngtable). We also need an Integer variable for columns in the table and a String variable for table headers.
• The range with data in the active sheet is determined with the UsedRange property of the Worksheet object and assigned to the variable “rng”.
• Then we convert the range into a table if there aren’t any tables in the active sheet. Tables in Excel VBA are represented by the ListObject object. ListObjects.Count returns the number of tables in the worksheet while ListObjects.Add creates a table from a range source. Remember to always reference the worksheet where the ListObject is defined (as in the code above) because the ListObject object is not fully qualified to stand alone as written here. Finally, we assign the table to the variable rngtable (declared earlier as a ListObject object variable).
• Now we start a loop through columns in the data set and for each column we perform the following tasks:
• Assign the header of the column to the variable “colname”
• Set the column width and first cell height to allow fitting the slicer there
• Add the slicer for that column with the name of the header
• The last step in the loop is the key function in this macro. Note that we first reference the SlicerCaches object for the workbook to add each individual slicer (a SlicerCache object) to the cache specifying the source table and the slicer name. The following line of code adds the slicer itself to the worksheet in the specified position. The code above is the short version and does not show the name of the parameters, but let’s break it down.
• ActiveSheet is the SourceDestination parameter
• The following parameter (Level) is omitted, hence the extra comma
• colname appears twice, one for Name and one for Caption. The Name of the slicer is used to recognize it among other slicers in the Excel workbook, while the Caption is just the text we see on the top of the slicer. It often makes sense to just use the same name for both.
• The last four parameters are Top, Left, Width, and Height, which specified the position of the slicer in the Excel worksheet in points

 

This is how we Add Slicers To Table Headers in Excel using VBA macros.

Other examples:

• How to Add Consecutive Numbers with Excel VBA macros
• How to Insert All Pictures In Folder with Excel VBA macros
• How to Clear Cells Based On Value with Excel VBA macros