Excrutiatingly slow insert/delete times using table and defined names

HMSTPI

New Member
Joined
Jul 31, 2017
Messages
11
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!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
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?
 

HMSTPI

New Member
Joined
Jul 31, 2017
Messages
11
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?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,846
Office Version
365, 2010
Platform
Windows, Mobile
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:

KB_02

New Member
Joined
Sep 7, 2018
Messages
25
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! :eek: 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,751
Messages
5,482,645
Members
407,356
Latest member
fabioargenton

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top