Trouble with Filters on a large file
 

Have a huge file, 29000 rows with 6 columns.

Ran a auto-filter but the filter stops at the "1001" row.

Thought that I was quite familiar with auto-filters until this.

Can anyone please help tell me what's worng or what I should do??


Thanks in Advance

Michael

Think 1000 is the limit for items to display in the autofilter droplist

Try Debra Dalgleish's coverage on this at her page:
http://www.contextures.com/xlautofilter02.html#Limits
for a possible workaround
(Look under "Limits to Dropdown Lists")
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Michael" wrote in message
...
Have a huge file, 29000 rows with 6 columns.

Ran a auto-filter but the filter stops at the "1001" row.

Thought that I was quite familiar with auto-filters until this.

Can anyone please help tell me what's worng or what I should do??


Thanks in Advance

Michael

Hi Max,

Thanks, your suggestion does address the limitation.
Strange that Microsoft is aware but not doing anything about the 1000 row
limit.
Just curious if there is any good reason for this.

Regards


Michael

"Max" wrote:

Think 1000 is the limit for items to display in the autofilter droplist

Try Debra Dalgleish's coverage on this at her page:
http://www.contextures.com/xlautofilter02.html#Limits
for a possible workaround
(Look under "Limits to Dropdown Lists")
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Michael" wrote in message
...
Have a huge file, 29000 rows with 6 columns.

Ran a auto-filter but the filter stops at the "1001" row.

Thought that I was quite familiar with auto-filters until this.

Can anyone please help tell me what's worng or what I should do??


Thanks in Advance

Michael

"Michael" wrote
Thanks, your suggestion does address the limitation.

You're welcome. Thanks for posting back.

Strange that Microsoft is aware
but not doing anything about the 1000 row limit.
Just curious if there is any good reason for this.

I've no comments here, sorry.
Maybe hang around awhile for possible insights from others.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Just my opinion...

I'm kind of surprised it's as big as 1000 unique entries. I bet that MS figured
that they had to limit it to something (at least 64k-1 <bg).

And by filtering using custom, you can lots--and if that's not enough, you could
use a formula in a helper column and filter on that.

(And sometimes just Edit|Find is quicker when each value shows up only once.)

Michael wrote:

Hi Max,

Thanks, your suggestion does address the limitation.
Strange that Microsoft is aware but not doing anything about the 1000 row
limit.
Just curious if there is any good reason for this.

Regards

Michael

"Max" wrote:

Think 1000 is the limit for items to display in the autofilter droplist

Try Debra Dalgleish's coverage on this at her page:
http://www.contextures.com/xlautofilter02.html#Limits
for a possible workaround
(Look under "Limits to Dropdown Lists")
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Michael" wrote in message
...
Have a huge file, 29000 rows with 6 columns.

Ran a auto-filter but the filter stops at the "1001" row.

Thought that I was quite familiar with auto-filters until this.

Can anyone please help tell me what's worng or what I should do??


Thanks in Advance

Michael




--

Dave Peterson