+ Reply to Thread
Results 1 to 3 of 3

Optimizing Data Tables (speed & stop)

  1. #1
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    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 Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    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 General Rules if you want my help. Not aimed at any person in particular:

    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
    Forum Contributor
    Join Date
    08-12-2019
    Location
    NYC
    MS-Off Ver
    2019
    Posts
    155

    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