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

Workaround?!

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… ~_~

EDIT:

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?

Other thoughts:

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.