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