+ Reply to Thread
Results 1 to 3 of 3

Optimizing Data Tables (speed & stop)

  1. #1
    Registered User
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    93

    Optimizing Data Tables (speed & stop)

    Hi there,

    I have 8 data tables in a complex excel sheet. I've noticed that the more I develop the spreadsheet (i.e. the more I add formulas & sheets), the slower these data tables get (even if the added formulas are unrelated), so two questions:
    - How can I optimize the speed of data tables? One of the techniques I use for my other macros is to deactivate screen updating by placing the VBA code before and after the macro itself. But how can I do something similar with data tables? (which are not associated with any VBA / macros, they just run when you click F9)? Are there other technics to speed up calculation?
    - I've just noticed that any action by user (even simply clicking anywhere in the sheet) during the calculation of a data table will automatically stop the refresh of data tables. I can't remember if it was always the case in my spreadsheet or if I changed a parameter without noticing but is there a way to prevent users from stopping the refresh? (or at least making it dependent on only 1 or 2 actions, such as pressing the ESC key, not any action)

    Thanks!

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,132

    Re: Optimizing Data Tables (speed & stop)

    It might pay you to remove most of your formulas from the tables.

    Then use a macro to enter the formulas in sequence, calculate the sheet, copy paste values and move to the next formula.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    93

    Re: Optimizing Data Tables (speed & stop)

    Thanks mehmetcik, so just to be sure, are you suggesting to create a macro to replace the default data tables of excel? Will this be faster? (I like the convenience of data tables)

    Thanks

+ 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. Process / speed issue on larger formulas - needs optimizing
    By purgamentum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2020, 04:38 PM
  2. Replies: 1
    Last Post: 08-04-2015, 11:35 PM
  3. Insert rows in several tables : how to speed up ?
    By Adrian692 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2013, 02:10 PM
  4. Feedback / ideas for optimizing this VBA code for speed
    By piedpiper11 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-11-2013, 06:09 PM
  5. [SOLVED] Optimizing code speed: Hiding rows with loops - looking for faster way
    By Taktiker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-07-2012, 04:24 PM
  6. Optimizing Data Entry - Help!
    By kimbellcf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2010, 10:01 AM
  7. Optimizing memory, speed: Arrays vs. Cells, etc...
    By T_o_n_y in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-02-2006, 04:15 PM

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