Strange Doings in Excel 1 (slow saves and autofilters)

[cgmackey]

Hi folks,

This is a follow up to a question I posted a couple of weeks ago. I’ve got an Excel workbook (Excel 2002). It’s got a main sheet with about 13,000 lines, another with about 2,000 lines, several small sheets and a lot of macros. The workbook was bloated and needed streamlining, so I have streamlined the file with user-defined functions and replaced many formulas with static data, which has cut the file size from 22 MB to about 14 MB and speeded up workbook calculation significantly.

My problem is that when I perform a “Save As” on the workbook, Excel hangs in a “not responding” state -- sometimes for several hours -- before finally completing the save and returning to “normal” function. Today, I confirmed that problem is not limited to network saves. As I write this, it has been saving to my C drive for about two hours.

During this time, I’ve monitored the Task Manager and observed the available memory (under Physical Memory on the Performance tab) marching steadily downward (with occasional ticks upward) from around 100,000 when I first looked to down below 70,000. It now seems to be rising again and is around 75,000. When I get to work tomorrow morning, it will probably be done and everything will look fine. Other times that I perform a save or “save as,” it takes between 10 and 20 seconds.

The auto-filter is acting similarly. Usually, when I turn on a filter, the filtered data appears almost instantly. But sometimes it will take as long as several minutes with no indication of what’s going on (it’s not auto-saving or anything else that I could discern).

Does anyone have any idea what Excel could possibly be doing for all this time if it’s not crashed dead? And are there any known issues that can cause these things to happen?

My company is planning to have many people using this workbook in the near future and I’m not sure I can declare it safe and predictable at this point.

Thanks,
Chris

 

[SydneyGeek]

If you set calculation to Manual, the Autofilter will be much quicker.

On large files I find that the "hang on Save" that you describe can be reduced by going to each sheet in turn and hitting Shift+F9 to recalc that sheet *before* the save. Sometimes the problem is completely solved. If not, the Shift+F9 routine will tell you which sheet is causing the problem.
I have a large model that uses a lot of INDIRECT formulas to consolidate data from other sheets. Sometimes they "freeze" and need to be freed up again by pressing F2 and TAB on the cells. (You'll have to unprotect before doing this). On large sheets that takes some time, but in the end it does deal with the problem.

Also, with large files I find it more reliable to duplicate the file in Windows Explorer than to do a Save As in Excel. The first reason is speed. Also, for reasons that aren't clear to me, any buttons attached to macros will reference the previous file if you Save As. Copying in Windows doesn't cause that problem.

BTW, you said many people will be using this file. You aren't planning to make it a shared workbook are you?

Denis

 

[cgmackey]

Thanks for the input, Denis. I always keep the sheet on manual calculation. I doubt that I’ve always calculated before saving, so I’ll try to remember to do that. I had been doing the occasional “save as” to create a backup and to tidy up and shrink the file, but I more or less decided to take the suggestion you were about to make about just copying the file in Windows. It’s not worth the hassle of being unable to use the sheet for hours on end.

The workbook will be rolled out as a template that will be used by many different people to track different studies. However, I don’t anticipate more than two or three people using any one workbook at the same time. Do I need to be concerned about this little bit of sharing?

 

[SydneyGeek]

However, I don’t anticipate more than two or three people using any one workbook at the same time. Do I need to be concerned about this little bit of sharing?

Yep.

There are potential issues with data loss, among other things. You also lose some features in shared workbooks. I'd recommend *not* sharing, and just letting users get in one at a time. That requires a bit of discipline so that user A doesn't open the workbook and then disappear for a few hours...

Run a Google search and see what you come up with.

Denis