MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Limit to Autofilter??


Posted by Ron on March 23, 2001 8:20 AM

We have a spreadsheet with approximately 2,500 rows that is continually added to. We would like to use the Autofilter option, but it appears as though there may be some limitation on the number of rows(records) that can be filtered. Some rows are starting to be left out of the autofilter operation. I've searched for an answer but haven't found any information on a possible limit. Any information is appreciated. Thanks.


Posted by Ian on March 23, 2001 9:46 AM

Syntax?

Ron,
You probably have already checked this, but are you sure that all the data is exactly the same on the excluded records. For example one is "Smith" and the other is " Smith"? I've used the filter on much larger sheets without issue.

Good luck.

Posted by Dave Hawley on March 23, 2001 7:12 PM

Re: Syntax?


Hi Ron
I believe the issue may be to do with the range needing to be sorted. As you know when you apply auto filters it displays the list in a sorted order and cannot sort beyond a certain rows amount.

Dave
OzGrid Business Applications

Posted by Mark W. on March 24, 2001 9:19 AM

> Some rows are starting to be left out of the autofilter operation. I've searched for an answer but haven't found any information on a possible limit.

Ron, if you're referring to number of entries in your AutoFilter drop down list then you should know that there is a limit -- 1000 unique entries. Your AutoFilter will function correctly if you have more that 1000 unique entries in your data list column, but you'll have to use a Custom selection or use an Advanced AutoFilter with a criteria range.