Autofilter doesn't include last record
Myrna Larson
recognize the boundaries of the list correctly: the last record isn't included
as part of the list. What that means is, regardless of what filter I apply, the
last record is always shown, whether it meets the filter criteria or not.
If I check the hidden name _FilterDatabase, I see it's one row short.
If I add a dummy record at the end, the filter begins to work correctly, and
the _FilterDatabase name is changed to add *2* rows to the reference (even
though I added only 1 record), so it's now correct.
But, of course, I now have to resort to some tricks, such as setting the font
for this record to white, so it isn't visible on the printed list.
I can't find any reference to this problem in the Knowledge Base. Has anyone
else seen this?
--
Myrna Larson
e-mail to: myrna...@csi.com
Thomas Ogilvy
I haven't seen it - but I may not have noticed that is was happening (or see
the last paragraph). A possible easier work around might be to select the
range and name it Database (although I can't test it - just a possible
solution).
If you select a cell in the data and do Ctrl+Shift+8, does the last row get
included? I
know I have seen articles in the knowledge base that talked about some old
problems (earlier versions) that only happened on particular rows or rows
that were multiples of certain numbers. This may be your month for
discovering the Weird Bug of the Century. " If the last row of a lists
falls on row such and such, it will be ignored by the autofilter. "
Regards,
Tom Ogivy
Myrna Larson wrote in message <36e2237a...@msnews.microsoft.com>...
Denny Campbell
I have run into this when using a SUBTOTAL formula in the range to count the
visible rows. Putting the formula column to either side of the filtered
data and not including it in the filter range, corrects the problem and the
formula still functions.
HTH
Denny Campbell
Grand Rapids, MI
Myrna Larson
wrote:
In case anyone is interested, I solved the problem I describe below. Turns out
that Excel is smarter than I thought when it comes to identifying lists, but
not quite smart enough to correctly handle one of my formulas!
I wanted the equivalent of a numbered list, but with the numbers changing
automatically as I changed the filter criteria. i.e. the first *visible* row
has a 1 in column B, the 2nd *visible* row has a 2, etc. I accomplished this by
using a SUBTOTAL formula (with the function set to COUNTA) in one of the
columns. It worked perfectly except for this problem with the last data record.
This is what is happening: when XL determines the boundaries of the list, it of
course looks for the empty rows and columns (or the edge of the worksheet) that
bound the list, but it evidently does a bit more than that.
After it's identified the list, it examines the last record. If it finds a
SUBTOTAL formula in any cell in this row, it "decides" that this row isn't a
data record: it's a summary row which should always be visible. So it defines
the _FilterDatabase name to exclude this row. (Evidently it looks only at the
last row, as I had a SUBTOTAL formula in every row of my list!)
The reason I got things working by adding a dummy record at the bottom was that
I didn't copy the SUBTOTAL formula down to this dummy row!
>I have a worksheet with a "list". When I autofilter this list, XL97-SR2 doesn't
>recognize the boundaries of the list correctly: the last record isn't included
>as part of the list. Regardless of what filter I apply, the last record is
>always shown, whether it meets the filter criteria or not.
Myrna Larson
wrote:
>I haven't seen it - but I may not have noticed that is was happening (or see
>the last paragraph). A possible easier work around might be to select the
>range and name it Database (although I can't test it - just a possible
>solution).
Tried that, doesn't work.
>If you select a cell in the data and do Ctrl+Shift+8, does the last row get
>included? I
Yes.
>know I have seen articles in the knowledge base that talked about some old
>problems (earlier versions) that only happened on particular rows or rows
>that were multiples of certain numbers. This may be your month for
>discovering the Weird Bug of the Century. " If the last row of a lists
>falls on row such and such, it will be ignored by the autofilter. "
I thought of that, too, and I moved the list around on the worksheet to test
that hypothesis -- no dice.
I finally figured it out, and it isn't a bug! Just a case of XL being "smart",
but not quite smart enough <g>.
I had used a SUBTOTAL formula in column A, to show a count of the visible rows,
i.e. the equivalent of a sequentially numbered list. The solution was to move
the SUBTOTAL formulas over to the right, leaving a blank column between them
and the list, and referencing that cell in column A, i.e. V2 has the
=SUBTOTAL(3,B$2:B2) formula, and A2 now has the formula =V2.
I got an email from another user (Denny Campbell??) who confirmed this. He had
used subtotals for the same purpose, and arrived at the same solution!