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.
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.
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
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.
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.
Excel AutoFilter Macros
Excel AutoFilter Macros is a post from Contextures Blog and is not allowed to be copied to other sites