Excel stops responding but only with this one workbook

[JeffGrant]

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.

 

[igold]

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]

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]

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.

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]

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]

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]

hi gold, as interim update, in the last 30 minutes

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]

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]

Have you noticed the size in mb of your book dropping...

 

[JeffGrant]

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.....