Default filter range

[Guest]

When applying autofilter, it seems to be defaulting to filter rows through
999. So even if data is entered only in a few rows, the next available row to
enter data is 1000 as long as the filter is applied.

For example, let's say there is data in rows 1-19. I turn on autofilter and
apply it. The only rows that contain filtered data are 2 and 17. So on the
screen I see rows 2 and 17, and the next row showing is 1000. I can't enter
new data in the blank rows between 20 and 1000 unless I turn off autofilter.

This is occuring in a workbook that we use as a template each week. It
didn't happen in older versions of the same workbook (both Excel 2003), and I
can't figure out what changed. There must be a setting somewhere that causes
autofilter to default to include rows 1-1000, instead of only the rows that
contain data. Help?

 

[Dave Peterson]

I'd turn off the autofilter (data|filter|autofilter), then select the range to
filter--not just the headers.

Then reapply data|filter|autofilter

 

[Guest]

Thanks, Dave, but I've tried that already, and it didn't work. Even if I
select the range of rows that have data before turning on autofilter, as soon
as I apply a filter in a column, it hides all rows through 999. Other ideas?

 

[Dave Peterson]

How are you applying that autofilter. Are you selecting the column or are you
selecting only the cells/rows you want filtered. My suggestion was to select
the complete range first: A1:X19 (or whatever the bottom right corner is).
Then apply data|filter|autofilter.

If that doesn't work, I'd try resetting the last used cell.

Visit Debra Dalgleish's site for some techniques:
http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

How are you applying that autofilter. Are you selecting the column or are you
selecting only the cells/rows you want filtered. My suggestion was to select
the complete range first: A1:X19 (or whatever the bottom right corner is).
Then apply data|filter|autofilter.

I was selecting the range of cells, just as you suggested. That is, I had
tried that as well as selecting the rows and not making a selection. None of
those solved the problem.

I can't follow the reset procedure because the "last cell" seems to be below
row 1000, and we have some reference data in Z1001 ff.

However, I did a workaround by copying the data in the "template" workbook
into a new workbook. That seems to have solved the problem. I'm still
curious, though, what caused it in the first place....

 

[Dave Peterson]

I don't have a guess why the suggestions didn't work--or what caused the
problem.

But glad you found a solution.