Filter Limitations

Lisa928

Board Regular
Joined
Jun 13, 2002
Messages
173
Does anyone know what the limit is for the filter drop down box? I have a list of products (sorted alphabetical) and the filter only shows products starting with A-D. Is the limit by the lines it searches to pull the unique occurrences, or is it by the number out output lines in the drop down?

Thanks in advance!
This message was edited by Lisa928 on 2002-10-10 11:49
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Here is an old post that may help


Quote
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. End quote


pll
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
OK I'm being picky (too much coffee this morning) :wink: ...

AutoFilter limits you to the first 999 unique values in the field of interest which can appear in the drop down list.

As Juan says, Advance Filtering is the option to turn to if you have more than 999 unique items to filter for.
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

On 2002-10-10 11:57, Tom Urtis wrote:
OK I'm being picky (too much coffee this morning) :wink: ...

AutoFilter limits you to the first 999 unique values in the field of interest which can appear in the drop down list.
Yes, VERY picky !
As Juan says, Advance Filtering is the option to turn to if you have more than 999 unique items to filter for.
I didn't say that :wink: (Altough it is true) I said that you could use the "Custom" option that appears in the dropdown list of the Autofilter...
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

On 2002-10-10 11:57, Tom Urtis wrote:
OK I'm being picky (too much coffee this morning) :wink: ...

AutoFilter limits you to the first 999 unique values in the field of interest which can appear in the drop down list.

As Juan says, Advance Filtering is the option to turn to if you have more than 999 unique items to filter for.

I'm being equally picky... the limit is 1000 unique entires. :)
 

Tom@CPC

Board Regular
Joined
May 22, 2002
Messages
209
Also...check to make sure there isn't a blank line in your table. The wizard will use the area up to the first blank line for its active range.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-10-10 12:10, Tom@CPC wrote:
Also...check to make sure there isn't a blank line in your table. The wizard will use the area up to the first blank line for its active range.

That would depend on the selection at the time of the application of the AutoFilter. If you had selected column D (see below) in its entirety (using the column heading button) the values, {5;6;7}, would appear in your AutoFilter drop down list...
Book1
CDEFGHIJKLMNO
1Field1
21
32
43
54
6
75
86
97
Sheet1

This message was edited by Mark W. on 2002-10-10 12:19
 

Tom@CPC

Board Regular
Joined
May 22, 2002
Messages
209
Thanks for pointing that out Mark, if you select the columns for the table this will alleviate that issue. Not to many people understand this method of selection though. I find it extremely useful in setting the datarange for my pivottables when the raw data is retrieved from an external database. This makes the range for data dynamic.

I was refering to the situations where a user is just in a cell in the table and selects auto filter.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top