Excel Macro: Removing All the Auto-Filters from an Active Workbook

Removing filters from many worksheets is annoying task. So I’ve made this Excel VBA macro.
This macro allows us to remove all filters from an active workbook.

Macro Example

Sub RemoveAllAutoFilter_ActiveWorkbook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.AutoFilterMode = False
  Next sh

End Sub

Description

Variable Declaration

The 3rd line declares an object variable named sh represents a Worksheet object.

Sub...

  Dim sh As Worksheet

The Worksheet object represents a worksheet in an Excel workbook.

obj-Excel-Worksheet

Starts Looping

The 5th line starts to loop through all the elements (=Worksheet) of a collection (=Worksheets).

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets

The Application.Worksheets property returns the Sheets collection object that represents all the worksheets (without chart-sheets) in the workbook.

obj-Excel-Application-Worksheets

The Worksheets property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Worksheets

Tuning Off the AutoFilter

The 6th line turns off the auto-filter by the Worksheet.AutoFilterMode property.

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.AutoFilterMode = False

obj-Excel-Worksheet-AutoFilterMode

We can set Worksheet.AutoFilterMode property to False to hide the arrows (=turn off the auto-filter).

Loops Back

The 7th line loops back to get the next worksheet.

Sub...

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.AutoFilterMode = False
  Next sh

After every worksheet is evaluated, this macro ends.

Sub RemoveAllAutoFilter_ActiveWorkbook()

  Dim sh As Worksheet

  For Each sh In Worksheets
    sh.AutoFilterMode = False
  Next sh

End Sub

Properties

http://www.relief.jp/itnote/archives/003497.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007