Leading the way in Microsoft Office Development

The status bar displays "Filter Mode"

When you filter a list (or table) on a worksheet the status bar changes to display the number of records found.

This message is not displayed if the status bar is changed before the filter is completed.

This message will be changed to "Filter Mode" if any of the following actions are performed:

1) A cell is formatted.

2) The contents of any cells are deleted.

3) Another worksheet is activated.

For more information please refer to this Knowledge Base Article (213886).

_filterdatabase - Worksheet Level Named Range

After you have applied the AutoFilter or used an Advanced Filter the corresponding table will be given the named range "_filterdatabase".

This named range is assigned automatically and will not appear in the Name Box or in any of the the (Insert > Name) dialog boxes.

This worksheet level named range is automatically reassigned to the corresponding table whenever you use the AutoFilter or Advanced Filter.

The "_filterdatabse" is not case sensitive but can be used to quickly select the whole table.

Select (Edit > GoTo) and type in "_filterdatabase" and press OK.

You could always define this named range manually to ensure that the correct cell reference is always entered into the "List range" field of the Advanced Filter.

If the named range is defined manually any cell can be selected before you select (Data > Filter > Advanced Filter).

Large range of cells

add an autofilter

filter on one particular column - all numbers in a particular range 0 < - < 10

select visible in a column block and shade

repeat for another range of numbers ??

AutoFilter Restrictions

If you have over 1000 unique elements in your list there are a couple of workarounds. Either split your list into two smaller lists that do not contain more than 1000 unique elements or extract a number of characters from the front of a column filter by that and then by the original column.

PivotTable Reports

Column labels are automatically used to create a pivottable report from your list.

These column labels will be used as field names.