Hi, all,

I recently started using Excel 2007 after having used older versions for
many years.

I am having a baffling issue with AutoFilters. I turn on filters, select a
criterion,
and find that the lowest row in my data table shows up regardless, even
though
it should have been filtered out.

I also note that the automatically created _FilterDatabase range on my sheet
ends
one row too soon. But if I edit or delete that named range, the problem
does not
resolve. Nothing different happens at all.

Help?

Next time you do this, look at that last row that should not be visible.

Notice the color of the row number for that row compared to the row numbers of
the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be filtered.

I'd remove the filter, and then select the range to filter. (I never let excel
guess!)

Then test again.

Did it work?



"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older versions for
many years.

I am having a baffling issue with AutoFilters. I turn on filters, select a
criterion,
and find that the lowest row in my data table shows up regardless, even
though
it should have been filtered out.

I also note that the automatically created _FilterDatabase range on my sheet
ends
one row too soon. But if I edit or delete that named range, the problem
does not
resolve. Nothing different happens at all.

Help?

--

Dave Peterson

Dave,

Yes, the last row is not included in the filter range. But nothing I can do
will get it to be included! That is the problem. If I turn off
autofiltering and turn it back on with the entire range selected manually,
Excel still leaves the last row unfiltered and creates a new
sheetname!_FilterDatabase range leaving out the last row.

Even if I manually select the blank row following the last row and turn on
autofilter with that selection active, the problem still occurs.

This is the silliest thing! I mean, millions of people are using Excel 2007
and autofilters. How can such a basic problem be biting me like this? I
don't get it. Nor am I a newbie. Oh, well, what other ideas do you have?

Much obliged.
S

"Dave Peterson" wrote in message
...
Next time you do this, look at that last row that should not be visible.

Notice the color of the row number for that row compared to the row
numbers of
the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be filtered.

I'd remove the filter, and then select the range to filter. (I never let
excel
guess!)

Then test again.

Did it work?



"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older versions for
many years.

I am having a baffling issue with AutoFilters. I turn on filters, select
a
criterion,
and find that the lowest row in my data table shows up regardless, even
though
it should have been filtered out.

I also note that the automatically created _FilterDatabase range on my
sheet
ends
one row too soon. But if I edit or delete that named range, the problem
does not
resolve. Nothing different happens at all.

Help?

--

Dave Peterson

I was going to include this in my first response, but I didn't want to add
clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning from data from
various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data just wouldn't be
included--even if I did it manually.

This was the only time (over and over and over) I've seen this problem.

My workaround (not quite a solution) was to add something to column A of the row
under the last row. Then filter the range through this lastrow+1. After I
applied the filter, I'd clear the cell (just clearcontents).

The only problem that the stupid filter included this lastrow+1 in its range!
So I'd see "blanks" as one of the options for the filtering dropdowns.

But I decided that I could live with that irritation to get the real last row
included.

Maybe it'll work for you and you'll lower your standards to allow this other
irritation.



"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But nothing I can do
will get it to be included! That is the problem. If I turn off
autofiltering and turn it back on with the entire range selected manually,
Excel still leaves the last row unfiltered and creates a new
sheetname!_FilterDatabase range leaving out the last row.

Even if I manually select the blank row following the last row and turn on
autofilter with that selection active, the problem still occurs.

This is the silliest thing! I mean, millions of people are using Excel 2007
and autofilters. How can such a basic problem be biting me like this? I
don't get it. Nor am I a newbie. Oh, well, what other ideas do you have?

Much obliged.
S

"Dave Peterson" wrote in message
...
Next time you do this, look at that last row that should not be visible.

Notice the color of the row number for that row compared to the row
numbers of
the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be filtered.

I'd remove the filter, and then select the range to filter. (I never let
excel
guess!)

Then test again.

Did it work?



"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older versions for
many years.

I am having a baffling issue with AutoFilters. I turn on filters, select
a
criterion,
and find that the lowest row in my data table shows up regardless, even
though
it should have been filtered out.

I also note that the automatically created _FilterDatabase range on my
sheet
ends
one row too soon. But if I edit or delete that named range, the problem
does not
resolve. Nothing different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

Thanks for the further elucidation. As a matter of fact, I also am creating
this sheet automatically from a macro that merges other sheets together.
I'm using a macro that started out from one of Ron de Bruin's merge VBA
examples - http://www.rondebruin.nl/tips.htm . But I edited it extensively
and have been using it for a couple of years in its present form under Excel
2002. Just now I'm trying to remake the entire thing for Excel 2007 because
of various annoyances and incompatibilities with my 2002 macros and ranges.
The merge macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you suggest in
the short run), but in all honesty I am too fastidious to be satisfied with
a kludge of that degree after having decided to dive in and fully convert my
sheets to optimize in native Excel 2007 form. I am still in jaw-drop mode
that such a problem can be happening. It's not as if Excel 2007 just came
out last week! Do you or does anyone have a suggestion for how I can get
Microsoft support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office instead.
Then at least I could also run under Linux.) :-)
--
Sarah

"Dave Peterson" wrote in message
...
I was going to include this in my first response, but I didn't want to add
clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning from data
from
various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data just
wouldn't be
included--even if I did it manually.

This was the only time (over and over and over) I've seen this problem.

My workaround (not quite a solution) was to add something to column A of
the row
under the last row. Then filter the range through this lastrow+1. After
I
applied the filter, I'd clear the cell (just clearcontents).

The only problem that the stupid filter included this lastrow+1 in its
range!
So I'd see "blanks" as one of the options for the filtering dropdowns.

But I decided that I could live with that irritation to get the real last
row
included.

Maybe it'll work for you and you'll lower your standards to allow this
other
irritation.



"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But nothing I can
do
will get it to be included! That is the problem. If I turn off
autofiltering and turn it back on with the entire range selected
manually,
Excel still leaves the last row unfiltered and creates a new
sheetname!_FilterDatabase range leaving out the last row.

Even if I manually select the blank row following the last row and turn
on
autofilter with that selection active, the problem still occurs.

This is the silliest thing! I mean, millions of people are using Excel
2007
and autofilters. How can such a basic problem be biting me like this? I
don't get it. Nor am I a newbie. Oh, well, what other ideas do you
have?

Much obliged.
S

"Dave Peterson" wrote in message
...
Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the row
numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on filters,
select a criterion, and find that the lowest row in my data table
shows up regardless, even though it should have been filtered out.

I also note that the automatically created _FilterDatabase range
on my sheet ends one row too soon. But if I edit or delete that
named range, the problem does not resolve. Nothing different
happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

Hi Sarah

Rather than Autofilter, I would try Insert tabTablemy table has headers.
This will insert the filter arrows in the header row by default, then use
them to filter your data.
I have never known the Table object to get it wrong.

--
Regards
Roger Govier

"Sarah H." wrote in message
...
Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other sheets
together. I'm using a macro that started out from one of Ron de Bruin's
merge VBA examples - http://www.rondebruin.nl/tips.htm . But I edited it
extensively and have been using it for a couple of years in its present
form under Excel 2002. Just now I'm trying to remake the entire thing for
Excel 2007 because of various annoyances and incompatibilities with my
2002 macros and ranges. The merge macro works fine as it was, but this
problem now arises with autofilter.

I appreciate your workaround idea (and might have to do as you suggest in
the short run), but in all honesty I am too fastidious to be satisfied
with a kludge of that degree after having decided to dive in and fully
convert my sheets to optimize in native Excel 2007 form. I am still in
jaw-drop mode that such a problem can be happening. It's not as if Excel
2007 just came out last week! Do you or does anyone have a suggestion for
how I can get Microsoft support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-)
--
Sarah

"Dave Peterson" wrote in message
...
I was going to include this in my first response, but I didn't want to
add
clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning from data
from
various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data just
wouldn't be
included--even if I did it manually.

This was the only time (over and over and over) I've seen this problem.

My workaround (not quite a solution) was to add something to column A of
the row
under the last row. Then filter the range through this lastrow+1. After
I
applied the filter, I'd clear the cell (just clearcontents).

The only problem that the stupid filter included this lastrow+1 in its
range!
So I'd see "blanks" as one of the options for the filtering dropdowns.

But I decided that I could live with that irritation to get the real last
row
included.

Maybe it'll work for you and you'll lower your standards to allow this
other
irritation.



"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But nothing I
can do
will get it to be included! That is the problem. If I turn off
autofiltering and turn it back on with the entire range selected
manually,
Excel still leaves the last row unfiltered and creates a new
sheetname!_FilterDatabase range leaving out the last row.

Even if I manually select the blank row following the last row and turn
on
autofilter with that selection active, the problem still occurs.

This is the silliest thing! I mean, millions of people are using Excel
2007
and autofilters. How can such a basic problem be biting me like this?
I
don't get it. Nor am I a newbie. Oh, well, what other ideas do you
have?

Much obliged.
S

"Dave Peterson" wrote in message
...
Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the row
numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on filters,
select a criterion, and find that the lowest row in my data table
shows up regardless, even though it should have been filtered out.

I also note that the automatically created _FilterDatabase range
on my sheet ends one row too soon. But if I edit or delete that
named range, the problem does not resolve. Nothing different
happens at all.

Help?

--

Dave Peterson

--

Dave Peterson


__________ Information from ESET Smart Security, version of virus
signature database 4540 (20091025) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4540 (20091025) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Roger,

Interesting -- I didn't know about Insert - Table. Glad to learn new
things. Guess what, though? It doesn't work. In fact, it's worse: it
leaves two wrong lines at the bottom instead of one. Not only that, but who
told "Table" to reset my column widths and add fill color? I very carefully
formatted my sheet just the way I wanted and already had my own fill color.
I appreciate Microsoft's offer of easy help with formatting, but it shouldn't
just take over what I have already without asking me! Sheesh.

This isn't the only bizarre trouble I'm having with Excel 2007, either --
but I had to start somewhere. (And Outlook 2007 BSOD'd my new machine until
I bought a new WLAN stick!) Anyway, I intend to tackle one problem at a
time until they're all solved. Thanks for terrific input; I'm sorry to have
to disappoint you as to the result so far.

I'd be willing to put the workbook somewhere others can view it, but it
contains rather sensitive data. I suppose I could create a mock-up version
of the data, but it would take me a bit of work. Let me know if I should do
something along those lines.
--
Sarah

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...

Hi Sarah

Rather than Autofilter, I would try Insert tabTablemy table has headers.
This will insert the filter arrows in the header row by default, then use
them to filter your data.
I have never known the Table object to get it wrong.

--
Regards
Roger Govier

"Sarah H." wrote in message
...
Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge macro
works fine as it was, but this problem now arises with autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning from
data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data just
wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through this
lastrow+1. After I applied the filter, I'd clear the cell (just
clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the real
last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But nothing
I can do will get it to be included! That is the problem. If I
turn off autofiltering and turn it back on with the entire range
selected manually, Excel still leaves the last row unfiltered and
creates a new sheetname!_FilterDatabase range leaving out the last
row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be biting
me like this? I don't get it. Nor am I a newbie. Oh, well, what
other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase range
on my sheet ends one row too soon. But if I edit or delete that
named range, the problem does not resolve. Nothing different
happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

See also
http://www.rondebruin.nl/table.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sarah H." wrote in message ...
Roger,

Interesting -- I didn't know about Insert - Table. Glad to learn new
things. Guess what, though? It doesn't work. In fact, it's worse: it
leaves two wrong lines at the bottom instead of one. Not only that, but who
told "Table" to reset my column widths and add fill color? I very carefully
formatted my sheet just the way I wanted and already had my own fill color.
I appreciate Microsoft's offer of easy help with formatting, but it shouldn't
just take over what I have already without asking me! Sheesh.

This isn't the only bizarre trouble I'm having with Excel 2007, either --
but I had to start somewhere. (And Outlook 2007 BSOD'd my new machine until
I bought a new WLAN stick!) Anyway, I intend to tackle one problem at a
time until they're all solved. Thanks for terrific input; I'm sorry to have
to disappoint you as to the result so far.

I'd be willing to put the workbook somewhere others can view it, but it
contains rather sensitive data. I suppose I could create a mock-up version
of the data, but it would take me a bit of work. Let me know if I should do
something along those lines.
--
Sarah

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...

Hi Sarah

Rather than Autofilter, I would try Insert tabTablemy table has headers.
This will insert the filter arrows in the header row by default, then use
them to filter your data.
I have never known the Table object to get it wrong.

--
Regards
Roger Govier

"Sarah H." wrote in message
...
Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge macro
works fine as it was, but this problem now arises with autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning from
data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data just
wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through this
lastrow+1. After I applied the filter, I'd clear the cell (just
clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the real
last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But nothing
I can do will get it to be included! That is the problem. If I
turn off autofiltering and turn it back on with the entire range
selected manually, Excel still leaves the last row unfiltered and
creates a new sheetname!_FilterDatabase range leaving out the last
row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be biting
me like this? I don't get it. Nor am I a newbie. Oh, well, what
other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase range
on my sheet ends one row too soon. But if I edit or delete that
named range, the problem does not resolve. Nothing different
happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

I had the problem with xl2k. I don't think it originated with xl2007.

I'm sure there's a link somewhere on the MS site that would allow you to contact
them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am creating
this sheet automatically from a macro that merges other sheets together.
I'm using a macro that started out from one of Ron de Bruin's merge VBA
examples - http://www.rondebruin.nl/tips.htm . But I edited it extensively
and have been using it for a couple of years in its present form under Excel
2002. Just now I'm trying to remake the entire thing for Excel 2007 because
of various annoyances and incompatibilities with my 2002 macros and ranges.
The merge macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you suggest in
the short run), but in all honesty I am too fastidious to be satisfied with
a kludge of that degree after having decided to dive in and fully convert my
sheets to optimize in native Excel 2007 form. I am still in jaw-drop mode
that such a problem can be happening. It's not as if Excel 2007 just came
out last week! Do you or does anyone have a suggestion for how I can get
Microsoft support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office instead.
Then at least I could also run under Linux.) :-)
--
Sarah

"Dave Peterson" wrote in message
...
I was going to include this in my first response, but I didn't want to add
clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning from data
from
various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data just
wouldn't be
included--even if I did it manually.

This was the only time (over and over and over) I've seen this problem.

My workaround (not quite a solution) was to add something to column A of
the row
under the last row. Then filter the range through this lastrow+1. After
I
applied the filter, I'd clear the cell (just clearcontents).

The only problem that the stupid filter included this lastrow+1 in its
range!
So I'd see "blanks" as one of the options for the filtering dropdowns.

But I decided that I could live with that irritation to get the real last
row
included.

Maybe it'll work for you and you'll lower your standards to allow this
other
irritation.



"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But nothing I can
do
will get it to be included! That is the problem. If I turn off
autofiltering and turn it back on with the entire range selected
manually,
Excel still leaves the last row unfiltered and creates a new
sheetname!_FilterDatabase range leaving out the last row.

Even if I manually select the blank row following the last row and turn
on
autofilter with that selection active, the problem still occurs.

This is the silliest thing! I mean, millions of people are using Excel
2007
and autofilters. How can such a basic problem be biting me like this? I
don't get it. Nor am I a newbie. Oh, well, what other ideas do you
have?

Much obliged.
S

"Dave Peterson" wrote in message
...
Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the row
numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on filters,
select a criterion, and find that the lowest row in my data table
shows up regardless, even though it should have been filtered out.

I also note that the automatically created _FilterDatabase range
on my sheet ends one row too soon. But if I edit or delete that
named range, the problem does not resolve. Nothing different
happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Very useful! Thank you, Ron.

--
Sarah

"Ron de Bruin" wrote in message
...
See also
http://www.rondebruin.nl/table.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sarah H." wrote in message
...
Roger,

Interesting -- I didn't know about Insert - Table. Glad to learn new
things. Guess what, though? It doesn't work. In fact, it's worse: it
leaves two wrong lines at the bottom instead of one. Not only that, but
who told "Table" to reset my column widths and add fill color? I very
carefully formatted my sheet just the way I wanted and already had my own
fill color. I appreciate Microsoft's offer of easy help with formatting,
but it shouldn't just take over what I have already without asking me!
Sheesh.

This isn't the only bizarre trouble I'm having with Excel 2007, either --
but I had to start somewhere. (And Outlook 2007 BSOD'd my new machine
until I bought a new WLAN stick!) Anyway, I intend to tackle one problem
at a time until they're all solved. Thanks for terrific input; I'm sorry
to have to disappoint you as to the result so far.

I'd be willing to put the workbook somewhere others can view it, but it
contains rather sensitive data. I suppose I could create a mock-up
version of the data, but it would take me a bit of work. Let me know if
I should do something along those lines.
--
Sarah

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...

Hi Sarah

Rather than Autofilter, I would try Insert tabTablemy table has
headers.
This will insert the filter arrows in the header row by default, then
use them to filter your data.
I have never known the Table object to get it wrong.

--
Regards
Roger Govier

"Sarah H." wrote in message
...
Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge macro
works fine as it was, but this problem now arises with autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning from
data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data just
wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through this
lastrow+1. After I applied the filter, I'd clear the cell (just
clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the real
last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But nothing
I can do will get it to be included! That is the problem. If I
turn off autofiltering and turn it back on with the entire range
selected manually, Excel still leaves the last row unfiltered and
creates a new sheetname!_FilterDatabase range leaving out the last
row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be biting
me like this? I don't get it. Nor am I a newbie. Oh, well, what
other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase range
on my sheet ends one row too soon. But if I edit or delete that
named range, the problem does not resolve. Nothing different
happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

Dave, et al.,

I believe I solved my problem! Your recital of your own experience with
what seems like the same problem was the key. You said you ran into this
with a sheet you recreated via a "mechanized routine." So I looked over my
sheet-merge macro and looked and looked.

I finally saw that I was turning on autofilter mode early in the macro,
before I merged the data from other sheets. It shouldn't make a difference,
but it does. I moved that step to near the end of the macro:

' Turn on auto-filter mode if off
With DestSh
If Not .AutoFilterMode Then .Range("a2").AutoFilter
End With

When that step was taken before the merge, Excel would update the active
range later but always had it one row short! The named range could not be
successfully adjusted manually (except by your trick of adding an extra cell
of dummy data).

Thanks to all! A very educational turn of events, even though frustrating
and time-consuming. You guys are great.

--
Sarah

"Dave Peterson" wrote in message
...

I had the problem with xl2k. I don't think it originated with xl2007.

I'm sure there's a link somewhere on the MS site that would allow you
to contact them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge
macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning
from data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data
just wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through
this lastrow+1. After I applied the filter, I'd clear the cell
(just clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the
real last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But
nothing I can do will get it to be included! That is the
problem. If I turn off autofiltering and turn it back on with
the entire range selected manually, Excel still leaves the last
row unfiltered and creates a new sheetname!_FilterDatabase range
leaving out the last row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be
biting me like this? I don't get it. Nor am I a newbie. Oh,
well, what other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase
range on my sheet ends one row too soon. But if I edit or
delete that named range, the problem does not resolve. Nothing
different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Sarah,
Many thanks for describing how you got around the problem.
So often we are "left in the dark" as to what the final outcome has been.
Thanks for sharing.

--
Regards
Roger Govier

"Sarah H." wrote in message
...
Dave, et al.,

I believe I solved my problem! Your recital of your own experience with
what seems like the same problem was the key. You said you ran into this
with a sheet you recreated via a "mechanized routine." So I looked over
my sheet-merge macro and looked and looked.

I finally saw that I was turning on autofilter mode early in the macro,
before I merged the data from other sheets. It shouldn't make a
difference, but it does. I moved that step to near the end of the macro:

' Turn on auto-filter mode if off
With DestSh
If Not .AutoFilterMode Then .Range("a2").AutoFilter
End With

When that step was taken before the merge, Excel would update the active
range later but always had it one row short! The named range could not be
successfully adjusted manually (except by your trick of adding an extra
cell of dummy data).

Thanks to all! A very educational turn of events, even though frustrating
and time-consuming. You guys are great.

--
Sarah

"Dave Peterson" wrote in message
...

I had the problem with xl2k. I don't think it originated with xl2007.

I'm sure there's a link somewhere on the MS site that would allow you
to contact them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge
macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning
from data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data
just wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through
this lastrow+1. After I applied the filter, I'd clear the cell
(just clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the
real last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But
nothing I can do will get it to be included! That is the
problem. If I turn off autofiltering and turn it back on with
the entire range selected manually, Excel still leaves the last
row unfiltered and creates a new sheetname!_FilterDatabase range
leaving out the last row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be
biting me like this? I don't get it. Nor am I a newbie. Oh,
well, what other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase
range on my sheet ends one row too soon. But if I edit or
delete that named range, the problem does not resolve. Nothing
different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


__________ Information from ESET Smart Security, version of virus
signature database 4541 (20091025) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4541 (20091025) __________

The message was checked by ESET Smart Security.

http://www.eset.com

I hope it continues to work for you.

That wouldn't work for me--I only applied the filter once.

"Sarah H." wrote:

Dave, et al.,

I believe I solved my problem! Your recital of your own experience with
what seems like the same problem was the key. You said you ran into this
with a sheet you recreated via a "mechanized routine." So I looked over my
sheet-merge macro and looked and looked.

I finally saw that I was turning on autofilter mode early in the macro,
before I merged the data from other sheets. It shouldn't make a difference,
but it does. I moved that step to near the end of the macro:

' Turn on auto-filter mode if off
With DestSh
If Not .AutoFilterMode Then .Range("a2").AutoFilter
End With

When that step was taken before the merge, Excel would update the active
range later but always had it one row short! The named range could not be
successfully adjusted manually (except by your trick of adding an extra cell
of dummy data).

Thanks to all! A very educational turn of events, even though frustrating
and time-consuming. You guys are great.

--
Sarah

"Dave Peterson" wrote in message
...

I had the problem with xl2k. I don't think it originated with xl2007.

I'm sure there's a link somewhere on the MS site that would allow you
to contact them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge
macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning
from data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data
just wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through
this lastrow+1. After I applied the filter, I'd clear the cell
(just clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the
real last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But
nothing I can do will get it to be included! That is the
problem. If I turn off autofiltering and turn it back on with
the entire range selected manually, Excel still leaves the last
row unfiltered and creates a new sheetname!_FilterDatabase range
leaving out the last row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be
biting me like this? I don't get it. Nor am I a newbie. Oh,
well, what other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase
range on my sheet ends one row too soon. But if I edit or
delete that named range, the problem does not resolve. Nothing
different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave,

Clarifying, in case it needs such -- I only apply the filter once too, if by
that you mean turn on filtering. But the point in rebuilding the sheet at
which I do that is key. My problem's now gone.

Oddly, I do not always have auto-filter mode turned off when I run my macro,
and in fact usually do *not* have it turned off, so I would not have
expected that snippet of code I showed to make any difference as to when in
the sheet-generation process it shows up. But it does make a difference. I
can't explain it.

--
Sarah

"Dave Peterson" wrote in message
...
I hope it continues to work for you.

That wouldn't work for me--I only applied the filter once.

"Sarah H." wrote:

Dave, et al.,

I believe I solved my problem! Your recital of your own experience with
what seems like the same problem was the key. You said you ran into this
with a sheet you recreated via a "mechanized routine." So I looked over
my
sheet-merge macro and looked and looked.

I finally saw that I was turning on autofilter mode early in the macro,
before I merged the data from other sheets. It shouldn't make a
difference,
but it does. I moved that step to near the end of the macro:

' Turn on auto-filter mode if off
With DestSh
If Not .AutoFilterMode Then .Range("a2").AutoFilter
End With

When that step was taken before the merge, Excel would update the active
range later but always had it one row short! The named range could not
be
successfully adjusted manually (except by your trick of adding an extra
cell
of dummy data).

Thanks to all! A very educational turn of events, even though
frustrating
and time-consuming. You guys are great.

--
Sarah

"Dave Peterson" wrote in message
...

I had the problem with xl2k. I don't think it originated with xl2007.

I'm sure there's a link somewhere on the MS site that would allow you
to contact them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge
macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning
from data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data
just wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through
this lastrow+1. After I applied the filter, I'd clear the cell
(just clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the
real last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But
nothing I can do will get it to be included! That is the
problem. If I turn off autofiltering and turn it back on with
the entire range selected manually, Excel still leaves the last
row unfiltered and creates a new sheetname!_FilterDatabase range
leaving out the last row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be
biting me like this? I don't get it. Nor am I a newbie. Oh,
well, what other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase
range on my sheet ends one row too soon. But if I edit or
delete that named range, the problem does not resolve. Nothing
different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Hi Sarah

There definitely was a problem with Autofilter in XL2007, and it did not
select properly the visible range of cells if you had your active cell
outside of the range of "data to be filtered", when you made the selection
for the Autofilter dropdown.
It was as if Excel could not work out the used range, and one manifestation
was that when trying to copy filtered data to another Sheet/area, it would
also include the "non visible" data.

What you describe, maybe a manifestation of that same problem.
Until a hotfix was available, I used the Table option, as the Table always
got the used range correct.

After downloading the hotfix, my problem with Autofilter cleared.
The hotfix is available at
http://support.microsoft.com/hotfix/...944&kbln=en-us

I don't know whether this was included in the SP2 release of XL2007 (I would
have imagined so).
What version are you running?
Mine is
(12.0.6504.5001) SP2 MSO(12.0.6425.1000)

--
Regards
Roger Govier

"Sarah H." wrote in message
...
Dave,

Clarifying, in case it needs such -- I only apply the filter once too, if
by that you mean turn on filtering. But the point in rebuilding the sheet
at which I do that is key. My problem's now gone.

Oddly, I do not always have auto-filter mode turned off when I run my
macro, and in fact usually do *not* have it turned off, so I would not
have expected that snippet of code I showed to make any difference as to
when in the sheet-generation process it shows up. But it does make a
difference. I can't explain it.

--
Sarah

"Dave Peterson" wrote in message
...
I hope it continues to work for you.

That wouldn't work for me--I only applied the filter once.

"Sarah H." wrote:

Dave, et al.,

I believe I solved my problem! Your recital of your own experience with
what seems like the same problem was the key. You said you ran into
this
with a sheet you recreated via a "mechanized routine." So I looked over
my
sheet-merge macro and looked and looked.

I finally saw that I was turning on autofilter mode early in the macro,
before I merged the data from other sheets. It shouldn't make a
difference,
but it does. I moved that step to near the end of the macro:

' Turn on auto-filter mode if off
With DestSh
If Not .AutoFilterMode Then .Range("a2").AutoFilter
End With

When that step was taken before the merge, Excel would update the active
range later but always had it one row short! The named range could not
be
successfully adjusted manually (except by your trick of adding an extra
cell
of dummy data).

Thanks to all! A very educational turn of events, even though
frustrating
and time-consuming. You guys are great.

--
Sarah

"Dave Peterson" wrote in message
...

I had the problem with xl2k. I don't think it originated with xl2007.

I'm sure there's a link somewhere on the MS site that would allow you
to contact them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge
macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning
from data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data
just wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through
this lastrow+1. After I applied the filter, I'd clear the cell
(just clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the
real last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But
nothing I can do will get it to be included! That is the
problem. If I turn off autofiltering and turn it back on with
the entire range selected manually, Excel still leaves the last
row unfiltered and creates a new sheetname!_FilterDatabase range
leaving out the last row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be
biting me like this? I don't get it. Nor am I a newbie. Oh,
well, what other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase
range on my sheet ends one row too soon. But if I edit or
delete that named range, the problem does not resolve. Nothing
different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


__________ Information from ESET Smart Security, version of virus
signature database 4543 (20091026) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4543 (20091026) __________

The message was checked by ESET Smart Security.

http://www.eset.com

I'd still keep a watchful eye out for the problem to come back.



"Sarah H." wrote:

Dave,

Clarifying, in case it needs such -- I only apply the filter once too, if by
that you mean turn on filtering. But the point in rebuilding the sheet at
which I do that is key. My problem's now gone.

Oddly, I do not always have auto-filter mode turned off when I run my macro,
and in fact usually do *not* have it turned off, so I would not have
expected that snippet of code I showed to make any difference as to when in
the sheet-generation process it shows up. But it does make a difference. I
can't explain it.

--
Sarah

"Dave Peterson" wrote in message
...
I hope it continues to work for you.

That wouldn't work for me--I only applied the filter once.

"Sarah H." wrote:

Dave, et al.,

I believe I solved my problem! Your recital of your own experience with
what seems like the same problem was the key. You said you ran into this
with a sheet you recreated via a "mechanized routine." So I looked over
my
sheet-merge macro and looked and looked.

I finally saw that I was turning on autofilter mode early in the macro,
before I merged the data from other sheets. It shouldn't make a
difference,
but it does. I moved that step to near the end of the macro:

' Turn on auto-filter mode if off
With DestSh
If Not .AutoFilterMode Then .Range("a2").AutoFilter
End With

When that step was taken before the merge, Excel would update the active
range later but always had it one row short! The named range could not
be
successfully adjusted manually (except by your trick of adding an extra
cell
of dummy data).

Thanks to all! A very educational turn of events, even though
frustrating
and time-consuming. You guys are great.

--
Sarah

"Dave Peterson" wrote in message
...

I had the problem with xl2k. I don't think it originated with xl2007.

I'm sure there's a link somewhere on the MS site that would allow you
to contact them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge
macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning
from data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data
just wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to column
A of the row under the last row. Then filter the range through
this lastrow+1. After I applied the filter, I'd clear the cell
(just clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the
real last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But
nothing I can do will get it to be included! That is the
problem. If I turn off autofiltering and turn it back on with
the entire range selected manually, Excel still leaves the last
row unfiltered and creates a new sheetname!_FilterDatabase range
leaving out the last row.

Even if I manually select the blank row following the last row and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be
biting me like this? I don't get it. Nor am I a newbie. Oh,
well, what other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in my
data table shows up regardless, even though it should have been
filtered out.

I also note that the automatically created _FilterDatabase
range on my sheet ends one row too soon. But if I edit or
delete that named range, the problem does not resolve. Nothing
different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Roger,

Thanks very much for that. It confirms my belief that something did change
from having used the same sheets and macro for a long time in Excel 2002.

I have Office 2007 Ultimate for Windows and am always fully updated. It's
SP2. (I just had to look around for about 5 minutes to figure out where to
find the version number, since they changed it from the tried-and-true
Help -- About for some reason. I hadn't noticed that before, as I've only
been using 2007 a few weeks. Also, you can't copy-and-paste the version
number anymore. Gee whiz.) I am running under a 64-bit version of Windows
7. I will download the HotFix and consider applying it. Thanks.

Version 12.0.6504.5001, SP2 MSO 12.0.6425.1000. I also have the German
Language Pack installed. I mention this because there is at least one known
bug I have uncovered as a result of having installed that: the "Analysis
ToolPak" and "Analysis ToolPak - VBA" Add-Ins switch without warning to
German -- though I run Excel in English -- at some point shortly after
installing Office and the Language Pack. That causes English-language
things that rely on the Analysis ToolPak to stop working. The fix I found
was to run the Office Diagnostics tool from the Resources pane under Excel
Options. It fixes the problem on Step 39 of 39 diagnostic tests! I had to
find that on my own: Microsoft Support, which did open a trouble ticket on
this for me, did not offer a solution by the time I found out how to fix
that myself.

--
Sarah


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Sarah

There definitely was a problem with Autofilter in XL2007, and it did not
select properly the visible range of cells if you had your active cell
outside of the range of "data to be filtered", when you made the selection
for the Autofilter dropdown.
It was as if Excel could not work out the used range, and one
manifestation was that when trying to copy filtered data to another
Sheet/area, it would also include the "non visible" data.

What you describe, maybe a manifestation of that same problem.
Until a hotfix was available, I used the Table option, as the Table always
got the used range correct.

After downloading the hotfix, my problem with Autofilter cleared.
The hotfix is available at
http://support.microsoft.com/hotfix/...944&kbln=en-us

I don't know whether this was included in the SP2 release of XL2007 (I
would have imagined so).
What version are you running?
Mine is
(12.0.6504.5001) SP2 MSO(12.0.6425.1000)

--
Regards
Roger Govier

"Sarah H." wrote in message
...
Dave,

Clarifying, in case it needs such -- I only apply the filter once too, if
by that you mean turn on filtering. But the point in rebuilding the
sheet at which I do that is key. My problem's now gone.

Oddly, I do not always have auto-filter mode turned off when I run my
macro, and in fact usually do *not* have it turned off, so I would not
have expected that snippet of code I showed to make any difference as to
when in the sheet-generation process it shows up. But it does make a
difference. I can't explain it.

--
Sarah

"Dave Peterson" wrote in message
...
I hope it continues to work for you.

That wouldn't work for me--I only applied the filter once.

"Sarah H." wrote:

Dave, et al.,

I believe I solved my problem! Your recital of your own experience
with
what seems like the same problem was the key. You said you ran into
this
with a sheet you recreated via a "mechanized routine." So I looked
over my
sheet-merge macro and looked and looked.

I finally saw that I was turning on autofilter mode early in the macro,
before I merged the data from other sheets. It shouldn't make a
difference,
but it does. I moved that step to near the end of the macro:

' Turn on auto-filter mode if off
With DestSh
If Not .AutoFilterMode Then .Range("a2").AutoFilter
End With

When that step was taken before the merge, Excel would update the
active
range later but always had it one row short! The named range could not
be
successfully adjusted manually (except by your trick of adding an extra
cell
of dummy data).

Thanks to all! A very educational turn of events, even though
frustrating
and time-consuming. You guys are great.

--
Sarah

"Dave Peterson" wrote in message
...

I had the problem with xl2k. I don't think it originated with
xl2007.

I'm sure there's a link somewhere on the MS site that would allow you
to contact them. But I bet it's difficut to find <bg.

"Sarah H." wrote:

Thanks for the further elucidation. As a matter of fact, I also am
creating this sheet automatically from a macro that merges other
sheets together. I'm using a macro that started out from one of Ron
de Bruin's merge VBA examples - http://www.rondebruin.nl/tips.htm .
But I edited it extensively and have been using it for a couple of
years in its present form under Excel 2002. Just now I'm trying to
remake the entire thing for Excel 2007 because of various annoyances
and incompatibilities with my 2002 macros and ranges. The merge
macro works fine as it was, but this problem now arises with
autofilter.

I appreciate your workaround idea (and might have to do as you
suggest in the short run), but in all honesty I am too fastidious
to be satisfied with a kludge of that degree after having decided
to dive in and fully convert my sheets to optimize in native Excel
2007 form. I am still in jaw-drop mode that such a problem can be
happening. It's not as if Excel 2007 just came out last week! Do
you or does anyone have a suggestion for how I can get Microsoft
support in on this?

(Gee, maybe I should have spent my effort re-coding for Open Office
instead. Then at least I could also run under Linux.) :-) -- Sarah

"Dave Peterson" wrote in message
...

I was going to include this in my first response, but I didn't
want
to add clutter if it wasn't necessary.

I had an xl2k workbook that I created from scratch each morning
from data from various sources (it was a mechanized routine).

I had that same exasperating problem. The last row of the data
just wouldn't be included--even if I did it manually.

This was the only time (over and over and over) I've seen this
problem.

My workaround (not quite a solution) was to add something to
column
A of the row under the last row. Then filter the range through
this lastrow+1. After I applied the filter, I'd clear the cell
(just clearcontents).

The only problem that the stupid filter included this lastrow+1
in its range! So I'd see "blanks" as one of the options for the
filtering dropdowns.

But I decided that I could live with that irritation to get the
real last row included.

Maybe it'll work for you and you'll lower your standards to allow
this other irritation.

"Sarah H." wrote:

Dave,

Yes, the last row is not included in the filter range. But
nothing I can do will get it to be included! That is the
problem. If I turn off autofiltering and turn it back on with
the entire range selected manually, Excel still leaves the last
row unfiltered and creates a new sheetname!_FilterDatabase range
leaving out the last row.

Even if I manually select the blank row following the last row
and
turn on autofilter with that selection active, the problem still
occurs.

This is the silliest thing! I mean, millions of people are using
Excel 2007 and autofilters. How can such a basic problem be
biting me like this? I don't get it. Nor am I a newbie. Oh,
well, what other ideas do you have?

Much obliged. S

"Dave Peterson" wrote in message
...

Next time you do this, look at that last row that should not be
visible.

Notice the color of the row number for that row compared to the
row numbers of the visible rows in the filtered range.

I bet it's not the same.

That means that this row wasn't included in the range to be
filtered.

I'd remove the filter, and then select the range to filter. (I
never let excel guess!)

Then test again.

Did it work?

"Sarah H." wrote:

Hi, all,

I recently started using Excel 2007 after having used older
versions for many years.

I am having a baffling issue with AutoFilters. I turn on
filters, select a criterion, and find that the lowest row in
my
data table shows up regardless, even though it should have
been
filtered out.

I also note that the automatically created _FilterDatabase
range on my sheet ends one row too soon. But if I edit or
delete that named range, the problem does not resolve.
Nothing
different happens at all.

Help?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson