In the process of writing my previous post, I found something wrong with Filter… when Column A contains formula starts with SUBTOTAL, Auto Filter refuses to take the last row.
- Note: Data Tab –> Filter does the same thing
What an unexpected result as there is no blank row in my range. @_@ Then I decided to enforce the selection (manually select the whole range) before I applied Filter to the range, Filter still stood firm to refuse the last row….
I wondered if the order of the formula has any impact… so I swapped column A with column B. Same result. I tried a few other functions (including AGGREGATE) to replace SUBTOTAL, Filter works normally…
For whatever reason, SUBTOTAL is not a good friend of Filter. @_@ Anyone knows why? Please share in comments. p.s. I am using Excel 2010
I tried select one extra row; and even whole columns. Neither worked. The not-so-perfect workaround is to input a space below the data before you turn on the Filter. However, there is always “Blanks” in your filter list. Another option is to copy one extra row of SUBTOTAL…. Weird enough too… ~_~
Thanks Debra Dalgleish so much that she pointed me to the right direction for the reason and workaround (please refer to the link of her blog post in comment below). It inspired me to modify the formula just a little bit to make it work.
Do you see the difference?
To play safe, it seems better to make it a habit to check if Filter includes the very last row of your intended range. Also pay attention when you turn on and off Filter.
hi good morning
I am in word Excel trying to put sequential numbers and skip 17 cells down the way it looks is in pages I don’t want to have to put down every number I would like someone to help me with he formula please .
HI HOW ABOUT SUBTOTAL 9 THAT DOESNT REFRESH WHEN USING ADVANCE FILTER IN EXCEL 2010?
I’ve run into this problem too, and there are a couple of workarounds in my blog post here:
What a pleasure to see your comment here! Thank you very much!
The Workaround 2 in your post works great. And it inspires to try to wrap the formula with (), and it works exactly in the same way.