Why don't all my data rows show up in auto-filter?

A

anromath

I've never experienced this before...

Spreadsheet has 18 columns and 3400 rows. For some reason, auto filter
stops at row 1593. I've tried copying and pasting to a new workbook, turning
filters on and off, using format painter to ensure the formats are all the
same, and various other random things.

Any ideas on what is wrong, and more importantly, how to fix?
 
P

Pete_UK

When you say it stops at 1593, what exactly do you mean? When you
apply a filter are all the row identifiers beyond 1593 coloured black
and these rows are visible? Or do you mean that you can only see
values in the pull-down up to that contained in row 1593? Excel only
displays the first 1000 (different) values in the pull-down.

Pete
 
D

David Biddulph

Switch off the Autofilter.

Select the range of rows and column you want to include in your filter
before switching on the Autofilter again.
 
A

anromath

Ah hah! That is exactly the problem -- there are more than 1000 different
values, so the values existing beyond row 1593 were not showing up in the
drop-down box.

I knew it had to be something simple and obvious. Thanks for your help Pete!
 
P

Pete_UK

Thanks for feeding back. I see that David has given you a link to the
Contextures site which explains how to get round it.

Pete
 
A

anromath

I don't this this will work for the person whose question I posted -- she is
a very, very unsophisticated user of Excel. But, I will keep it in mind for
myself. Thanks for teaching me a new trick!
 
D

David Biddulph

Yes, it's a useful one. I have a spreadsheet at home that has many more
names than will show up in the drop-down list, but I have a helper column
using the LEFT() function to give the first letter of the name, which I use
for an initial filter by that column to get the number of names to what
autofilter can handle.
 
Top