+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Performance problems (sandclock) when I filter or "unfilter" tables in a file

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    42

    Performance problems (sandclock) when I filter or "unfilter" tables in a file

    Dear Excel Gurus,

    I have an Excel 2007 file that has about 15 tabs. It is about 5Mb total in size. It has one large table (about 7000 rows and about 20 column) and a bunch of pivots and smaller tables that "feed off" the large table. The file also has one pretty simple macro in it (I recorded it: it just sorts one of the smaller tables based on several criteria)

    My problem is that whenever have to "filter" or "unfilter" either the large table or one of the smaller tables, it takes many minutes for Excel to do it. (It shows a "sand clock" for a really long time). This happens even when I am working with one of the smaller tables (tables that have about 300 rows and between 2 and 10 columns). By "filter" I mean that I select specific values in a specific column, so table shows only those rows that have one of the selected values in that column. By "unfilter" I mean that I select "all" values in the column.

    The tables do have some simple computations (countifs, countblanks and the like) in some of the columns

    I cannot post the file here because it contains corporate data.

    Does anyone have any ideas on how I can improve the performance of Excel? I do not think I had this problem before. I am wondering if I somehow slowed things down by inserting the macro and saving the file as macro-enabled.


    Studiosa.
    P. S. I will be away from my computer next week, so I apologize in advance if I do not respond right away.
    Last edited by studiosa; 04-06-2012 at 05:07 PM. Reason: Spelling errors - sorry!

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Performance problems (sandclock) when I filter or "unfilter" tables in a file

    Hi Studiosa,

    I had a similar problem recently albeit with a much larger workbook (quater of a million rows and about 100 columns in the data page alone). It was taking about 20 minutes to apply or remove just one filter! And that was with "auto calculate" turned off so the formulas on other tabs weren't calculating during that time.

    Turns out one of the columns had conditional formatting to highlight certain dates if they fell before dates in another column and that's what was causing the problem.

    Not saying it's definitely the same thing on your workbook, but worth a punt as the rest of your workbook sounds WELL within the limits of Excel. Have a check for conditional formatting, if there is any, remove it and see if that helps.

    S.
    Last edited by Spencer101; 04-07-2012 at 03:55 AM.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Performance problems (sandclock) when I filter or "unfilter" tables in a file

    also, check to see if you are using any array-type formulae, or ant finctions like index() that are based on an entire column. If you have any of these, try to reduce the range to just what you need, or simplify them, even if that means uing helper columns...just a thought
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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