Excel stops responding but only with this one workbook

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have a workbook which is 34MB, has 15 Sheets but is heavily laden with formulas and a little bit of formatting.

For quite some time I have been experiencing issues where Excel Stops responding, i get the not responding message at the top of the screen, the fan kicks in after about 20 seconds and then Excel will continue.

If I only open the file, and do an immediate close and don't save, that process alone can take up to 40s.

I have deleted all add-ins, removed all of my anti virus protection, removed everything from auto startup, done three Office 365 online repairs.

I am running an Acer Aspire 5 notebook, with 20Gb Ram external monitor.

On a regular basis, I will get a macro cant run error for a custom group, and then when I delete the macro from the group and re-add everything is fine.

But tonight I have this new problem. The macros don't even appear for me to re-add them.

It only happens with this workbook, so clearly it is related to this workbook.

As a precaution, I do a manual backup multiple times per day.

I am at a loss and getting really frustrated.

Has anybody got some ideas why this workbook is giving me so much grief?

Really hoping that the good folk on this forum have some ideas.

J.


UPDATE: I just did a File Open Open & Repair, I the Macros now appear in the box below.

All suggestions are graciously received.

1629548878872.png
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Just for a little more information, on the bottom left of the workbook in the status bar there is an item called "Workbook Statistics" could you post a pic of that. If you do not have it showing, right click the status bar and check the appropriate box.
 

EXCEL MAX

Well-known Member
Joined
Nov 11, 2020
Messages
508
Office Version
  1. 2016
Platform
  1. Windows
I would first do this... Open Task manager=>Performance and look what's happening with RAM and CPU.
If chart shows big values then...
Second, try to remove formulas and formats on each sheet out of used range unnecessary (blank) cells.
Third, check conditional formatting ranges. Resize conditional formatting formulas to used ranges.
Conclusion:
Manipulate only with used range, especially calculation.
Hope this was helpful.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Just for a little more information, on the bottom left of the workbook in the status bar there is an item called "Workbook Statistics" could you post a pic of that. If you do not have it showing, right click the status bar and check the appropriate box.
Thank you igold. I have never seen this before.

1629671513226.png


Just looking at this, I know that the workbook is heavily loaded with formula, but not 800,000 + worth, nor is there 4M + cells of data. I will start the hunt on locating this today and irradiating them.

Because most data starts in A1, my plan will be to go to the the last used row and column and delete everything manually from the there to XEQ1048576.

Unless you have a more efficient solution.

This is a real eye opener......
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I would first do this... Open Task manager=>Performance and look what's happening with RAM and CPU.
If chart shows big values then...
Second, try to remove formulas and formats on each sheet out of used range unnecessary (blank) cells.
Third, check conditional formatting ranges. Resize conditional formatting formulas to used ranges.
Conclusion:
Manipulate only with used range, especially calculation.
Hope this was helpful.
Hi Excel Max,

ALL suggestions are helpful. I am pulling my hair out over this one.

As an immediate part of the solution, I have removed all conditional formatting because now that you mentioned it, it did see that conditional formatting could be problem about a month ago.

As for your second suggestion, that also ties in with igold's suggestion, so I am on the hunt effectively immediately.

I can't thank you both enough......
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
As suggest by Excel Max, I would start looking for "Phantom Ranges". With only 15 sheets, it may easiest to go to each page and hit CTRL + END and that will take you to the the cell that Excel thinks is the last cell with data on that sheet. If you find that these are way out of whack then you need to delete the Phantom Ranges. There are lots of ways to do that. Instead of me trying to explain it here you may just want to Google it.

Good Luck! Let us know if you need more help or just some feedback and what you find if you are inclined to do so.
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

hi gold, as interim update, in the last 30 minutes
1629675547942.png


And has this made a difference, just by deleting phantom ranges. I still have three sheets that for some reason going to CTRL + END and then deleting anything and everything anywhere near this cell is not making any difference to the CTRL + END. but I will keep working on it. I just wanted to give you an quick update.
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I have had issues getting rid of phantom ranges. It seems like they don't want to leave. One thing I have found is that immediately after you delete a phantom range, stop, save the book, close it and then reopen it. That often does the trick. Even you have cells highlighted as soon as you hit delete. Save it and close it.
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Have you noticed the size in mb of your book dropping...
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
196
Office Version
  1. 365
Platform
  1. Windows
1.Doing the immediate Save after the delete, sees to be doing the trick.
2. Yes, the overall size is getting smaller.
3. Even though, I didn't have hundreds of conditional formats, because my formals are copied down through the range, I found several with #ref in them, so i am guessing that Excel could not resolved them, which would definitely cause a problem.

4. One other tell tale sign I have of a problem is if I do an insert/delete of a row into a table, then while it is doing that, i have enough time to make a cup of coffee. I suspect this is highly related to the over all problem.....
 

Forum statistics

Threads
1,148,177
Messages
5,745,194
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top