Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excrutiatingly slow insert/delete times using table and defined names

  1. #1
    New Member
    Join Date
    Jul 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Excrutiatingly slow insert/delete times using table and defined names

    I have a file that is taking longer and longer to insert/delete/move cells/columns/rows now up to at least 10 minutes EVERY SINGLE TIME! I have tried absolutely EVERYTHING that I can find and am at my wits end.

    I started from scratch to create a budget model that would provide forecasting options without the need to worry about formula inconsistency and, really, have everything working like I want -- except this one MAJOR issue.
    The file is really quite simple at this point as I am still flushing out all of the forecasting options:
    only 3 data sheets
    almost everything is in table format
    about 200 defined names; about 1/2 are just cell references, the rest using if, sumif, index, match, indirect and offset functions
    the longest table is only 350 rows -- and not all formulas
    There are NO:
    references to entire columns or rows
    no conditional formats
    no external connections
    no shapes or images
    Here is what I have tried:
    recreating the file / copying data over to new file
    deleted data validations
    made everything the same font / removed bold, italics, shading, and borders
    removed add-in and the formulas that used it
    turned off unneeded background programs
    saved the file locally
    rebooted the computer
    saved the file as .xslb

    Absolutely NOTHING has worked-- in fact it just seems to get worse every time I try another insert/delete. I am on Windows 7, Excel 2016 64-bit with 2G memory and 118GB free space on C:. The Resource Monitor shows that, when acting on the delete/insert, only 12% CPU and 31% memory is in use.

    I'm praying you can give me more ideas.

    Thanks so much!

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,898
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    Do you have any VBA code; especially event macros lake a Worksheet_Change procedure?

    Can you upload a desensitized copy of your workbook on a file share site and post a link here?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Jul 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names


  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,898
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    That file share site requires an account login.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  5. #5
    New Member
    Join Date
    Jul 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    shoot -- it didn't tell me that .. I can't say that I've ever shared a file other than from one-drive that I guess would always require a login. Where /how else would I do that?

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,035
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    I think the OP has probably just not marked the file for sharing.
    HMSTPI, if not try using www.box.com or www.dropbox.com as we can probably guide you through sharing a file on those sites.
    Last edited by MARK858; Oct 3rd, 2018 at 02:45 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    Jul 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    try this -- using personal instead of company that wouldn't allow me to share with anyone.
    https://1drv.ms/x/s!ArBWadL4zwPHryNzYIMsewHU_Dt5

  8. #8
    New Member
    Join Date
    Sep 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    Admittedly, I haven't looked at your file yet, but what size is the file as a whole?

    I had the same problem with a sheet I am working on and the problem ended up being that I had copied multiple formulas to the bottom of several rows (bringing them down 1.4 million rows...). This brought the file up to 24+MB in size! I replaced nearly all of the formulas with VBA Macros and that dropped it back down into the KB range. I also had to look at the formulas I did keep to make sure they were not populating otherwise empty cells with formulas (which they were) which was driving the size up as well. The bigger the file size, the slower it got.

    Don't know if that info will help you or not, but it's worth a look.

  9. #9
    New Member
    Join Date
    Jul 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    file size is < 700KB

  10. #10
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,898
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excrutiatingly slow insert/delete times using table and defined names

    FWIW, It wouldn't open for me.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •