Excel 2000 - Autofilter Limit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NanoMark - 19 Oct 2006 00:44 GMT

I have a spreadsheet with over 3500 rows (not really a lot) but the
Autofilter drop down list does not show them all. It only shows approximately
2400 rows. Is there a limit to the number of rows that autofilter can be used
on?

Reply to this Message

Max - 19 Oct 2006 00:59 GMT

Select the entire col(s) before you apply autofilter. Any blank cells/rows
in-between data lines will "break" autofilter.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I have a spreadsheet with over 3500 rows (not really a lot) but the
> Autofilter drop down list does not show them all. It only shows approximately
> 2400 rows. Is there a limit to the number of rows that autofilter can be used
> on?

Reply to this Message

NanoMark - 19 Oct 2006 23:30 GMT

Thanks Max, but no blanks or breaks in the data. Apparently there is a 1000
record limit to the drop down autofilter list (see post after yours).

> Select the entire col(s) before you apply autofilter. Any blank cells/rows
> in-between data lines will "break" autofilter.
> > I have a spreadsheet with over 3500 rows (not really a lot) but the
> > Autofilter drop down list does not show them all. It only shows approximately
> > 2400 rows. Is there a limit to the number of rows that autofilter can be used
> > on?

Reply to this Message

Max - 20 Oct 2006 00:35 GMT

No prob. Thanks for feedback.
Guess I mis-read. My focus was on your line:

> .. It only shows approximately 2400 rows.

Gord's reading & response was spot on <g>

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks Max, but no blanks or breaks in the data. Apparently there is a
> 1000
> record limit to the drop down autofilter list (see post after yours).

Reply to this Message

Gord Dibben - 19 Oct 2006 01:10 GMT

The autofilter drop-down list will show only 1000 items.

You have to work around by using "Custom" and choose from options there.

Contains..........Greater Than..........Equal to...............and more.

See Debra Dalgleish's site for more workarounds to this limit.

http://www.contextures.on.ca/xlautofilter02.html#Limits

Gord Dibben MS Excel MVP

>I have a spreadsheet with over 3500 rows (not really a lot) but the
>Autofilter drop down list does not show them all. It only shows approximately
>2400 rows. Is there a limit to the number of rows that autofilter can be used
>on?

Reply to this Message

NanoMark - 19 Oct 2006 23:32 GMT

Thanks Gord. This answers my question, even if I don't like it! I think the
custom option will be the way to go, it's just harder to train users on that
option.

Regards,

> The autofilter drop-down list will show only 1000 items.
>
[quoted text clipped - 12 lines]
> >2400 rows. Is there a limit to the number of rows that autofilter can be used
> >on?

Reply to this Message

Gord Dibben - 20 Oct 2006 00:23 GMT

Thanks for the feedback.

This has been a "can you change it" item for quite a while.

I don't know if 2007 beta has made any modifications or not.

Gord

>Thanks Gord. This answers my question, even if I don't like it! I think the
>custom option will be the way to go, it's just harder to train users on that
[quoted text clipped - 18 lines]
>> >2400 rows. Is there a limit to the number of rows that autofilter can be used
>> >on?

Reply to this Message