Sometimes Excel is so smart you just want to punch it in the face. Larry pointed out in my Accumulating Visible Cells post that the Autofilter never hides the last row. Gosh darn it if he wasn’t right. I did a little googling on the matter and Myrna Larson says:
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!)
To fix this little bit of meddling on the part of Excel, replace the SUBTOTAL formula with
=SUBTOTAL(9,$B$2:B2)
I guess it only does its magic when the first function of the formula is SUBTOTAL. The double negative sign seems to trick it appropriately. Good catch Larry (and Myrna).
As always, you come through with the clever solution!
I dont understand what is the function of 9 in formula. Is it stand for subtotal.
And is the same formula works in Access.?
Grateful to everybody
Wow, thanx so much.
Was really looking for this one.
Isn’t XL flabergastedly wierd… (If there is such an expression)
I have changed the subtotal function this way: =VALUE(SUBTOTAL(9;AD2326:AO2326))
So the last row is not visible any more, it works fine for me.
This flaw had my colleagues and myself feeling desparate for a long time. Thanks for the working solution, much better now!
Hero!
That — subtotal( etc) in the dummy column tip fixed my report.
I use the subtotal function to indicate whether the row is visible to enable sumifs of visible cells.
Without the — in front of the subtotal function, it seems that the refresh and reapply filters do not work!
Cheers,
Nick