XL: AutoFilter List Hides Rows Even After Information Cleared

ID: Q123782

The information in this article applies to:

SYMPTOMS

When you use the AutoFilter feature on a list, and then use the Clear command to remove information from the list, the rows that contained the cleared information may be hidden (even though they no longer contain information that is relevant to the list).

CAUSE

Each time you use the AutoFilter feature, the list that the filter applies to is redetected. This behavior allows the filter to account for items you may have added to the list. The size of the list can be expanded down and to either side, but it cannot be reduced.

This behavior is by design of Microsoft Excel.

WORKAROUND

To work around this behavior, use either of the following methods.

Method 1

Remove information from the end of the list by clicking Delete (rather than clear) on the Edit menu.

Method 2

Delete the information, remove, and then reapply the filter.

REFERENCES

Excel 97

For more information about using AutoFilter to filter a list, click the Office Assistant, type "autofilter," click Search, and then click to view "Display a subset of rows in a list by using filters."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office
               Program or Component

Excel 5.0

For more information about using AutoFilter to filter a list, click the Search button in Help and type: 

   autofilter
Press ENTER and then double-click "Filtering a list using AutoFilter" to go to that topic.

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 97 XL97 XL7 XL5 

Keywords          : kbdta xlui xllist 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 7, 1999