Hide & Unhide (Filter) Columns with a Slicer or Filter Drop-down Menu - Excel Campus
23

Hide & Unhide (Filter) Columns with a Slicer or Filter Drop-down Menu

Bottom line: Have you ever wanted to use a slicer or filter to hide/unhide columns on a regular worksheet range or table?  Normally our data has to be in a pivot table to make use of the column filtering capabilities, but this simple macro allows us to hide and unhide worksheet columns with a slicer or pivot table filter.

Skill level: Advanced

The Filter Columns Macro Thumbnail 640x360

Pivot table slicers and filters are a great way to hide and unhide columns in a pivot table.  This allows us to display only relevant columns for the current time period, project, category, etc.  Hiding and unhiding columns can help reduce horizontal scrolling, which can be time-consuming on large worksheets.

However, pivot tables aren’t a good solution for all applications and reports.  We cannot use pivot tables for input sheets, and reports with customized or complex column structures.

So, I wrote this macro that combines the best of both worlds.  The Filter Columns macro allows us to use a slicer or pivot table filter to hide and unhide columns on a worksheet.  This means our data does NOT need to be in a pivot table to use the functionality of a slicer or filter for columns.

Example Uses for the Column Filter Macro

One simple example is a report with a month/quarter/year column structure, where you want to hide/unhide all month columns for a specific quarter.  This report might be a forecast/budget input sheet, or any worksheet where you would NOT use a pivot table.

The animated screencast below shows the macro in action.  The slicers are filtering (hiding/unhiding) regular worksheet columns.

Filter Columns with Slicer Macro - Quarterly Report Example

Click here to view the animation in your browser.

Another example is an inventory or time allocation sheet that has a lot of columns for projects, locations, regions, etc.

Filter Columns with Slicer Macro - Project Allocation Sheet Example

How Does the Filter Columns Macro Work?

The Filter Columns macro uses a simple pivot table for the interactive controls only.  That pivot table contains one field with a list of the unique values from the header row range (column criteria) for the report.

When the user clicks a slicer item or changes the pivot’s filter drop-down menu, the Worksheet_PivotTableUpdate event is fired.  The event automatically triggers a macro to run any time a user interacts with a slicer or pivot table drop-down filter menu.  This is awesome because it gives us unlimited possibilities for using slicers as interactive controls.

The Filter Columns Macro Explained

The worksheet event then calls the Filter Columns macro.  The macro loops through each cell in the header row range (column criteria) and checks if that item is selected in the slicer/filter.  If the pivot item is checked (visible), then the column is made visible (unhidden).  If the item is not checked then the column is hidden.

The end result is similar behavior to what you would expect if you filtered the columns area of a pivot table.  However, our data does NOT need to be in a pivot table.  It can be in a regular worksheet range or Excel Table.

The Filter Columns Macro Code Setup

There are two macros at work here.  The first is the Worksheet_PivotTableUpdate event.  This macro runs whenever the user makes a change to the slicer or pivot table filter drop-down menu.  The PivotTableUpdate event is stored in the code module of the worksheet that contains the pivot table.

PivotTableUpdate Event in Sheet Code Module

The Filter_Columns macro contains parameters/arguments that are passed through from the PivotTableUpdate event.  These parameters specify the header row range, report sheet name, pivot sheet name, pivot table name, and pivot field name.  Using parameters allows us to reuse the Filter Columns several times throughout the workbook.

Here is the code for the PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Run the filter columns macro when the pivot table or slicer is changed
    
    Select Case Target.Name
        Case "PivotTable1"
            Call m_FilterCol.Filter_Columns("rngQuarter", "Report", "Report", "PivotTable1", "Quarter")
    End Select
    
End Sub

Here is Filter_Columns macro code.  The macro is located in the m_FilterCol code module in the example workbook file.

Sub Filter_Columns(sHeaderRange As String, _
                    sReportSheet As String, _
                    sPivotSheet As String, _
                    sPivotName As String, _
                    sPivotField As String _
                    )

Dim c As Range
Dim rCol As Range
Dim pi As PivotItem
 
    
    'Unhide all columns
    Worksheets(sReportSheet).Range(sHeaderRange).EntireColumn.Hidden = False
    
    'Loop through each cell in the header range and compare to the selected filter item(s).
    'Hide columns that are not selected/filtered out.
    
    For Each c In Worksheets(sReportSheet).Range(sHeaderRange).Cells
        
        'Check if the pivotitem exists
        With Worksheets(sPivotSheet).PivotTables(sPivotName).PivotFields(sPivotField)
            On Error Resume Next
            Set pi = .PivotItems(c.Value)
            On Error GoTo 0
        End With
            
        'If the pivotitem exists then check if it is visible (filtered)
        If Not pi Is Nothing Then
            If pi.Visible = False Then
            
                'Add excluded items to the range to be hidden
                If rCol Is Nothing Then
                    Set rCol = c
                Else
                    Set rCol = Union(rCol, c)
                End If
            End If
        End If
        
        'Reset the pivotitem
        Set pi = Nothing
        
    Next c
    
    'Hide the columns of the range of excluded pivot items
    If Not rCol Is Nothing Then
        rCol.EntireColumn.Hidden = True
    End If
    
End Sub

How Do I Setup Filter Columns in My Workbook?

I included setup instructions in the example Excel file that you can download below.

Setup Instructions for Filter Columns Macro

There are about six steps to setting this up in your own workbook.  You should have knowledge of how to use and modify macros.  If you are new to macros, checkout my free 3-part video series on getting started with Macros & VBA.

I also have an article and video on how to run event based macros when the user takes an action in the workbook.

Download the Example File

The example file contains the setup instructions, macros, and the two examples I showed above.

Filter Columns Macro Examples.xlsm (36.5 KB)

Here is another example of using multiple slicers on the same sheet, based on a question from Michael in the comments below.

Filter Columns Macro Examples - Multiple Slicers.xlsm (41.6 KB)

What else can we use this for?

I feel like there are a lot of possibilities with this macro, and the use of the slicers or pivot filter drop-down menus as interactive controls.  What will you use this for?  Please leave a comment below with suggestions or questions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 23 comments
Logonna - June 13, 2017

Hi,

I have a question in this context.

I managed a file (checklist) used by different people where are listed lots a items (in sveral sheets).
In each sheet, certains items concerned the whole group but some of them concern specific groups. (Precision : all items for group A, some items for group B, others (different than linked to B) for Group C, … It can have until 7 groups. Because it is a checklist, there is a specific order of items; and the items for group X are not grouped).
Because list of items are important by sheet, it is asked to hide not applicable items. For example, items of group A must be seen (if A selected), all the others must be hidden.

I need to use autofilter. But I don’t know how to define rows to be hidden.
I tried to dim RowUp and RowDown the 1st and last lines under filter line. Then I tried to define range(RowUp:RowDown).Select and the function to hide. The problem is to set the “range(RowUp:RowDown).Select”… Could you help me to solve it or to propose another solution ?
Regards,
Logonna

Reply
UMESH SUDHAKAR MAHADIK - May 9, 2017

Hi,

I have a query.

I want to consolidate some monthwise data from 15 sheets to one sheet.
I want to consolidate one by one data in one sheet of respective month

How to create macro for this.

I’m trying to record macro but I’m facing some issues,
Like

My consolidation sheet is “Sheet1”

To copy current month’s data from “sheet 2” I need to filter for specific month then select some columns & need to copy & paste in “sheet 1″.

This thing works perfectly.

But problem is that how to copy data of” sheet 3, 4,5&so) in “Sheet 1”

Reply
Timo - February 26, 2017

Hi Jon,

Thanks for all the info! I was wondering if there is an easy way to add multiple filters for each column.

For example I have a list of projects (across the columns) which I want to filter by fee total and type of contract. Is there an easy way to do this?

Thanks!

Reply
Michael - January 19, 2017

Hi Jon,

This is a fantastic tool; thank you so much! Question for you: how would I go about setting up two of these in the same worksheet? I have 50 different entities with 12 different items I am looking at over the course of 15 years.

Thank you again!

Reply
    Jon Acampora - January 20, 2017

    Thank you Michael! You can setup multiple slicers in the sheet. I added a file in the downloads section above that does this. It basically calls a macro to unhide all the rows first, then loops through each pivot table on the sheet and runs the Filter_Columns macro for each pivot table.

    If your sheet has other pivot tables that you do not want to run the filter on, then you can add an IF statement with some logic to test if the pivot table name starts with a prefix that you want to use for the pivot tables that apply filters. The Instr function can be used to test this, or the Left function.

    I hope that helps. Let me know if you have any questions.

    Reply
Hampus - January 16, 2017

Hi, I get a runtime error when I click on the slicer.

Application-defined error or object defined error

When I try to debug it highlights this code

Worksheets(sReportSheet).Range(sHeaderRange).EntireColumn.Hidden = False

Reply
    Jon Acampora - January 20, 2017

    Hi Hampus,
    That is probably due to the variables for the report sheet (sReportSheet) and header range (sHeaderRange) not being set. You will need to change these in the Worksheet_PivotTableUpdate event code to match the names of your sheet and range reference.

    The following line of code calls the Filter_Columns macro and sets those parameters with sReportSheet = “Report” and sHeaderRange = “rngQuarter”. You will need to change these to match the name of the sheet that your pivot table is on, and the range reference to the range where the filter criteria are stored. I use a named range for this (rngQuarter) but you can use a regular range reference as well (“A1:F1”). The problem with the regular range reference is that you will need to change the code every time you move criteria range on the sheet. Named ranges help prevent this maintenance and errors.

    I hope that helps.

    I hope that helps.

    Reply
AL - December 12, 2016

Jon,

Thanks so much for posting this! I have been stumped on my current project and this seems to be the fix!

Any chance of a video being made for this one?

Reply
Raheel Raaj - September 7, 2016

Thank you for sharing Jon its really a great Tip…

Reply
Tri Teguh - September 7, 2016

Hai Jon,

Thanks, is very helpfull

Reply
Chaminda Basnayake - September 7, 2016

Hi Jon,

Thank you for sharing, This is great.

Chaminda

Reply
Todd M. Paxman - September 7, 2016

This is great! Seems you could also use this along two or total columns: one that totals all columns (or by quarter, by year, etc.), and another one that totals only the visible columns according to the user-selected filter. Then the selection could be used to include or exclude certain months, categories, etc. from the subtotals/totals from the evaluation.

Reply
    Jon Acampora - September 7, 2016

    Hi Todd,
    Great suggestion! Since we can’t use the SUBTOTAL or AGGREGATE functions to ignore hidden columns, we could use your approach to display subtotals for the visible columns only. Thanks!

    Reply
Mark Howell - September 7, 2016

Hi Jon,

I love the possibilities for this! I can’t wait to put it into use. I did notice that the downloaded file does not show the Q1 – Q4 headers in Step 4. Instead, it shows ellipses (three dots). Any ideas how to fix that?

Thanks again,
Mark in Austin

Reply
    Jon Acampora - September 7, 2016

    Hi Mark,
    Are you using a Mac version of Excel by chance? Unfortunately, these macros will not work with the Mac versions of Excel. Excel 2011 for Mac does not support slicers, and Excel 2016 for Mac doesn’t seem to support worksheet events. The file should work on all Windows versions of Excel from 2007 to 2016. Let me know if you have any questions. Thanks!

    Reply
    Daryl Duren - September 7, 2016

    Mark/Jon my file showed ellipses also. I resized the frame in rows C6:F6 to see the quarter names.

    Reply
      Jon Acampora - September 7, 2016

      Thanks Daryl! I didn’t fully comprehend Mark’s comment. I will update the file and resize the slicer to make it larger.

      This can happen with Windows scaling and different monitor resolutions, but making the slicer slightly bigger should prevent most of those issues.

      Thanks again and have a good one!

      Reply
Ben - September 2, 2016

Thank you for sharing !
What app do you use to make your nice gifs ?

Reply

Leave a Reply: