+ Reply to Thread
Results 1 to 4 of 4

clear cache to reduce sheet recalc time

  1. #1
    Registered User
    Join Date
    08-15-2008
    Location
    Chicago
    Posts
    2

    clear cache to reduce sheet recalc time

    i have a worksheet with formulas in 15,000 cells; i have set the calculation to maunal so that you have to press F9 to calculate the sheet. \

    sometimes, it takes 45 seconds to calc the whole sheet, other times, it takes 5 minutes. how do i clear the cache, or what do i have to do so that it calcs 45 seconds each time.

    i don't want the application to think, it would be better if those 15,000 cells can be returned in a fraction-of-a-second. any ideas/tips? thanks.
    Last edited by VBA Noob; 08-15-2008 at 12:46 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Welcome to the forum.

    The amount of time it takes to recalculate depends on the number of 'dirty' cells (those needing recalculation). If you do Ctrl+Alt+F9, I'd expect it would require the longer time every time.

    Perhaps the formulas could be redesigned to calculate in less time.

  3. #3
    Registered User
    Join Date
    08-15-2008
    Location
    Chicago
    Posts
    2

    Red face dirty cells set to clean

    is there vba to set dirty cells to clean and to somehow set the clean cells as dirty? hmmm...
    or how would you set excel options to calculate dirty cells only?
    or to re-calculate clean cells along with dirty cells?

    and would this work for my purpose?
    dim objVar as excel.application
    set objVat = nothing

    thanks
    Last edited by pacificwaters; 08-15-2008 at 03:45 PM.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    ... how would you set excel options to calculate dirty cells only?
    That's what Excel normally does. That's what F9 does.
    or to re-calculate clean cells along with dirty cells?
    Ctrl+Alt+Shift+F9 -- see Keyboard Shortcuts.
    and would this work for my purpose?
    Please Login or Register  to view this content.
    It declares an object variable, which start out life as Nothing, and then explicitly sets it to Nothing, which accomplishes ... nothing.

+ 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