Slow to close workbook if another workbook open

Sinon

Active Member
Joined
Aug 6, 2015
Messages
298
Hi all! Not really sure what supporting info to provide but I have a spreadsheet that has a rather odd behaviour.

The workbook has a lot of formulae and a sheet that has over 7000 rows. That sheet has about 10 columns of formulae which include INDEX/MATCHES referring to a couple of tables in other sheets. There's also data validation with 3-5 values. There are no macros in the workbook but it's saved as an xlsb. It does not refer to any other workbooks.

Everything works fine and it's not too slow when amending various cells. The problem arises when closing the file. It takes a good 2-3 minutes for the file to close, regardless of whether it saves or not. However, this only happens if there is another workbook open. I tested various scenarios and, as long as this workbook is the only one that is open, it closes in seconds. As soon as I open another workbook, even a blank one, it takes 2-3 minutes. I tried it both on our shared server and moving it to my own machine and the behaviour is consistent. The workbook is meant to be used with at least another workbook open so this is an issue as, depending on the user's machine, it may even crash and close all Excel files.

I think the formulae are not the issue as closing the workbook works as expected if it's the only excel workbook open. For the life of me, I don't know what is causing this issue. My last resort is to start fresh and redo the workbook as the workbook has been recently modified but in place for a couple of years. However, that would be an extensive job and take most of a day and I'm reticent to do this.

Any suggestions would be greatly appreciated, or if anyone has encountered this before.

Thanks!!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi All,

I see this forum is old & had no replies.

I have had this frustrating issue & spent ages trying to diagnose what has been causing it & searching through forums.

We are on version Microsoft 365 64 Bit.

My model is 32MB with a lot of SUMIF & INDEX/MATCH formulas. It takes up to 1 1/2 minutes to close with another blank workbook open, but closes instantly if no other workbooks are open.

I have tried the following:

- Delete sheet by sheet to try & diagnose the cause
- removed all customized styles
- removed all conditional formatting
- de-activated all Add-Ins

The model closes slightly faster everytime I delete a sheet, so I'm assuming it has to do with the size of the model.

I would be very happy to include some VBA "On Close" which forces saving the workbook & then forces a quick close.

Any suggestions, questions or even just simply confirming this is normal behavior for Excel would be really appreciated

Thanks

Perky
 
Upvote 0
Any suggestions, questions or even just simply confirming this is normal behavior for Excel would be really appreciated!!!!
 
Upvote 0
Office 365 does seem to have some problems with "wasting" time. I suggest trying it when you are offline and not connected to the internet, that often seems to cure the office 365 time problems. It would at least give a clue as to where the problem is.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top