MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Autofilter : limit to rows ?


Posted by Kees on November 19, 2001 6:03 AM

Is there a limit to how many rows autofilter will work on ?. I have a list of about 3500 items, when I select the whole sheet and apply an autofilter, it only takes the first 1400 or so items. If I select a range further down the list and apply an autofilter there, I get rows which did not appear in the first autofilter but still only 1400 odd lines. I am using Excel97


Posted by Rick M on November 19, 2001 6:12 AM

There must be a blank line or field after the last line the filter applied. Check to see if there is any missing data.

Posted by Kees on November 19, 2001 6:20 AM

Nope...No blank lines.
1. Select the whole sheet (i.e. all records). Autofilter will look as far as Row 1505
2. Select row 2, apply autofilter, will look as far as row 1506
3. Select row 1000, apply autofilter, will look as far as row 2405

Weird....

Posted by Mark W. on November 19, 2001 6:23 AM

If you're refering to the number of rows visible
in the drop down list it's 1000. AutoFilter
will operate on lists greater than 1000 rows in
length, but you must select "Custom" and enter
the value(s) of interest.