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.