Removing Filters and Unhiding Rows and Columns on Multiple Worksheets

Rob has a workbook that contains multiple worksheets. He would like to know the easiest way to remove filters and unhide rows and columns in all the worksheets at once.

One would think that it would be possible to do this manually by building a "selection set" of all the worksheets you want to affect, and then removing filters. While you can use this approach for unhiding rows, you cannot affect filters—once you select more than a single worksheet, the Filter tool (on the Data tab of the ribbon) is no longer selectable.

This means that you must use a macro to do the work—unless you want to remove filters one worksheet at a time. Here is a short little macro that will remove any filters applied to any worksheets in the workbook:

Sub RemoveFilters()
    Dim wks As Worksheet

    Application.ScreenUpdating = False
    For Each wks In ThisWorkbook.Worksheets
        If wks.AutoFilterMode Then wks.AutoFilterMode = False
    Next wks
    Application.ScreenUpdating = True
End Sub

If the hidden rows and columns are a result of the filters you applied, those rows and columns should be visible after removing all the filters. If there are other rows and columns that are manually hidden and that you want displayed, you can use the following version of the macro:

Sub RemoveFiltersUnhide()
    Dim wks As Worksheet

    Application.ScreenUpdating = False
    For Each wks In ThisWorkbook.Worksheets
        With wks
            If .AutoFilterMode Then .AutoFilterMode = False
            .Rows.Hidden = False
            .Columns.Hidden = False
        End With
    Next wks
    Application.ScreenUpdating = True
End Sub

This version removes filters and then unhides any rows and columns previously hidden.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3036) applies to Microsoft Excel 2007, 2010, 2013, and 2016.