Excel AutoFilter Macros

Written by Debra Dalgleish from Contextures Blog

There are two types of AutoFilters in Excel – worksheet AutoFilters, and List (Table) AutoFilters. If you’re using Excel AutoFilter macros, be sure they’re designed for the type of Autofilters that are in your workbook.

Excel AutoFilters

In Excel, if you have a range of cells with a heading row, and rows with data, you can use the AutoFilter feature.

There are basic instructions, and a few AutoFilter tricks and tips on my Contextures website.

Worksheet AutoFilters

In the olden days of Excel, there was only one type of AutoFilter – a worksheet AutoFilter.

And to add to our suffering in the olden days, you could only have one worksheet AutoFilter on each sheet. I know, it was rough!

You can still use worksheet AutoFilters, and the animated screen shot below shows how they work – one at a time.

There are filters in List B
I clicked in List A, and clicked the Filter command
Excel automatically removed the filters from List B
I clicked the Filter command again, and then Excel added filters to List A

worksheetautofilter01

List AutoFilters

In Excel 2007 and later versions, there’s a second type of AutoFilter – a List AutoFilter.

If you format the range as a named Excel table, the AutoFilter feature is turned on automatically. You’ll see arrows in the heading row, and you can use those to filter one of more of the columns.

You can have multiple List AutoFilters on each worksheet, as you can see in the screen shot below.

filtermacrocriterialist02

AutoFilter Macros

Overall, the two types of AutoFilters work pretty much the same way, when you’re manually selecting things on the worksheet.

However, you might run into problems when you’re using Excel macros to work with the AutoFilters.

If you’ve just got one named table on a worksheet, you can probably use a macro that’s designed for either type of AutoFilter
If you’ve got multiple named tables, be sure to use a macro that’s designed for List AutoFilters.

Show All Records

Here are two small AutoFilter macros that show all the records in a filtered range.

This macro is designed for Worksheet AutoFilters, but could also clear the filters in a filtered Excel table.

Sub ShowAllRecordsWS()
  With ActiveSheet.AutoFilter
    If .FilterMode Then
      .ShowAllData
    End If
  End With
End Sub

This macro is designed for List AutoFilters. It can’t clear the filters in a non-Table range on the worksheet.

Sub ShowAllRecordsList1()
Dim Lst As ListObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set Lst = ws.ListObjects(1)

  With Lst.AutoFilter
    If .FilterMode Then
      .ShowAllData
    End If
  End With
End Sub

More Excel AutoFilter Macros

You can get more AutoFilter macros on my Contextures site.

For the worksheet type, go to the AutoFilter Macros page. There’s code on the page, and a sample file to download.

For the List (Table) type, go to the List AutoFilter Macros page. There’s code on the page, and a sample file to download.

The latest macro on the List macros page shows a message, with details on the filters that are currently applied.