Autofilter includes extra row

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

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

On Tue, 25 Aug 2009 19:16:09 +0100, "George Gee"
wrote:

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

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 wrote:

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

--

Dave Peterson

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Your workaround is as nutty as the one I used.

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

George Gee wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson