Excel 2007 AutoFilter problem

[Sarah H.]

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]

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.]

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]

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.]

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.)

 

[Roger Govier]

Hi Sarah

Rather than Autofilter, I would try Insert tab>Table>my 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

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




__________ 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

 

[Sarah H.]

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.

 

[Ron de Bruin]

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

 

[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>.

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 H.]

Very useful! Thank you, Ron.

--
Sarah

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

 

[Sarah H.]

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.

 

[Roger Govier]

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

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




__________ 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

 

[Dave Peterson]

I hope it continues to work for you.

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

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 H.]

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.

 

[Roger Govier]

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/KBHotfix.aspx?kbnum=948944&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

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




__________ 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

 

[Dave Peterson]

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

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 H.]

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.