Autofilter and Subtotal
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!
[...] I found another workaround, that's much better than the dummy row solution. Dick Kusleika was subtotalling filtered rows, and discovered that he could fix the problem by typing two minus signs in front of the SUBTOTAL [...]