+ Reply to Thread
Results 1 to 5 of 5

Error in filtering table - additional rows shown, rows hidden when filter removed

  1. #1
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Angry Error in filtering table - additional rows shown, rows hidden when filter removed

    I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.

    When I start using filters, I run into problems:

    Step 1: Filter by Unit, condition (e.g.) Unit_23
    Excel shows in the status bar the following message: 437 of 2050 records found.
    Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
    Step 3: Clear the filter of Unit
    Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!


    Can anyone out there help me understand what is happening and what should I do?

    Thank you for your support.Attachment 301726
    Click on the star if you think I helped you

  2. #2
    Registered User
    Join Date
    07-03-2013
    Location
    Montreal, QC
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Error in filtering table - additional rows shown, rows hidden when filter removed

    Hello,

    I have tried your file and have the same problem. I coverted the table back to range. Recreated the table with the same results.
    I noticed a formula in column H "=SUBTOTAL(103,[@[Report_No]])". I copied the data from column A to G into another sheet. Created a table with it and had no problems with the filters. I then expanded the table with the formula in column H and the problem came back. I do not know why or how but I think there is an issue with the formula.

    Regards

  3. #3
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Error in filtering table - additional rows shown, rows hidden when filter removed

    It looks like, but why? :-)
    I need that formula for my Median calculation.

    Thanks for looking at the issue

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Error in filtering table - additional rows shown, rows hidden when filter removed

    its to do with having subtotal in the last row
    http://blog.contextures.com/archives...el-autofilter/
    why are you using subtotal like that?
    anyway
    apparently the work around is to trick excel into not noticing its a subtotal() row
    change formula in col h
    to

    =--SUBTOTAL(103,Reports!$A2)
    Last edited by martindwilson; 03-04-2014 at 11:46 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Error in filtering table - additional rows shown, rows hidden when filter removed

    Martin
    This solved my problem. Thank you.

    I need to do a Median calculation and for that I filter and then compute the median for the visible rows only.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Remove Rows based upon list of search terms, copy removed rows to new sheet
    By sam103329 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-19-2015, 08:47 PM
  2. Remove Rows based upon list of search terms, copy removed rows to new sheet
    By sam103329 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-26-2013, 10:32 PM
  3. Hidden Rows being Overridden wihen Filtering & Copying
    By Pomrloo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-11-2013, 04:06 PM
  4. Delete Rows hidden by either Auto Filter and Advanced Filter
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 10:29 AM
  5. filtering problem, wrong rows removed
    By rinser in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-17-2009, 06:03 AM

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