+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 [Auto]Filter ignores bottom row of range

  1. #1
    Registered User
    Join Date
    07-23-2010
    Location
    Geneva, Switzerland
    MS-Off Ver
    Excel 2007
    Posts
    1

    Exclamation Excel 2007 [Auto]Filter ignores bottom row of range

    Imagine a 3x5 table as follows, starting in cell A1:

    RowNumber; Region; SalesValue
    1;North;$20
    2;North;$11
    3;South;$44
    4;North;$29
    5;South;$11

    Place the cursor in cell A1 and select "Filter".
    Everything works fine, Excel correctly detects 3 cols and 6 rows (inc. header).

    Now make the row numbers 'dynamic' as follows:
    Replace the hard coded row number in A2 with the following formula:
    =SUBTOTAL(3,$C$2:C2), then copy down.
    The RowNumbers are now 'dynamic', i.e. they are always consecutive, even when filtered.

    Place the cursor in cell A1 and select "Filter".
    Excel detects 3 cols and ONLY 5 ROWS (inc. header).
    The last row is never detected.
    Manually defining the list range (Advanced) doesn't work either.
    Excel readjusts the range to exclude the last row,

    Surely this is a bug?
    Last edited by tbrookes; 07-23-2010 at 03:32 PM. Reason: spelling correction

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Excel 2007 [Auto]Filter ignores bottom row of range

    If you copy the SUBTOTAL to the row beneath the last row the filter will work - it's an odd one which I can't say I've ever seen explained fully.

  3. #3
    Registered User
    Join Date
    12-23-2013
    Location
    Budapest, Hungary
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Excel 2007 [Auto]Filter ignores bottom row of range

    Same at me. It is the Subtotal that makes the Excel "forget" the bottom rows. I haven't found a proper explanation nor an easy way to avoid it. The only thing you can do is to is avoid using Subtotal in tables where you want to use AutoFilter.
    Or, put an empty row below the bottom of your table, that contains only a fix number in the column of Subtotal. So when the AutoFilter checks if you have Subtotal in bottom row it won't find it.

    I found one other trick, but it's not a nice solution. Put the column with subtotal in another sheet (that you might hide), and in your RowNumber column use just a reference to that column with the formula =INDEX(Bgrsheet!A:A,ROW(),1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1