Autofilter includes extra row

[George Gee]

Just recently Autofilter has started including the last row of data in the
sheet, even though this row does NOT contain the filter criteria.

Anything I can do?

George Gee

 

[Gord Dibben]

Do you have a hidden blank row or a very narrow blank row above the last
row?

Deal with that.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Filter your data once more.

Now take a look at the row numbers.

You'll notice that the range that was filtered (and now visible) has blue row
numbers.

I'm guessing that that last row number is still grey. That means that excel
doesn't see it as part of the autofilter range.

You could remove the filter arrows and select the entire range and reapply the
filter to see if that works.

===
Just a warning. I had some code that created a new workbook that a filtered
sheet in it. Sometimes (always???), the lastrow wasn't included in the filter
range.

I ended up adding a new last row and filling it with bogus data. I applied the
data|filter|autofilter and then emptied that row with the bogus entries.

Sometimes, when the filtering worked perfectly, I'd get "blanks" as an option
when the real data didn't contain blanks in that field. But I learned to live
with that. (I think that was xl2k, too.)

 

[George Gee]

Hi Gord, no hidden or narrow rows in the sheet.
Hi Dave, all is as you say re row number colours.
I removed the autofilter arrows and re-selected the range with the same
result.

However, I've solved the problem.
I deleted the last row, inserted a new row in a purely random position,
copied the deleted row data from a copy of the workbook, and pasted it in
the
new row. I then sorted the sheet to my requirements, and all is working OK!

I don't pretend to understand what happened, but at least it's back to
normal.

Many thanks
George Gee

 

[Dave Peterson]

Your workaround is as nutty as the one I used.

Here's hoping that it doesn't break for you.