Excel 2007 spreadsheet hangs

Lindahq,

It's not clear to me exactly what you were doing.

(1) You mention a very large workbook and then talk about a number of spreadsheets. Are these worksheets within the very large workbook or are they other workbooks?
(2) You mention an Open and Repair attempt. Did you run this simply because of the failed run or did you get an error/hang opening a workbook? (If so, what was the error and which workbook?
(3) Did you reboot after the Excel hang before attempting to re-open the workbook? (I ask this because it's very problem you were having memory issues and following the hang Windows may not have released all of the memory leaving insufficient to re-open the workbook.)
(4) You're running 32bit Excel 2007, correct? What version of Windows? What type of Excel file? How much memory on your PC? Were any other programs open at the time?
(5) 394MB is a lot of spreadsheet! Why does it need to be so big? Any possibility that you could move at least some of it to, say, Access?

Thanks,
Brian.

Apologies, point (3) went off the rails. It should read...

(3) Did you reboot before attempting to re-open the workbook? (I ask this because it's very possible that you were having memory issues. Following the hang, Windows may not have released all of the used memory thus leaving insufficient to re-open the workbook.)

I am on Windows XP Pro.

The workbook opens without any problems.  It hangs when I try to edit one worksheet in the workbook.

I did the repair because of the hang.

I did a hard reboot after the repair.  

All of the spreadsheets are in the same workbook.

Unfortunately no portion of the workbook can be moved to Access as others use the workbook and they are not Access users.

I can send the file if that would help.

Forgot to add...2GB RAM Intel Core Duo CPU T2350 @ 1.86 GHz
120 GB HDD with 27 GB free

Lindahq,

Thanks for all that info.

It hangs when I try to edit one worksheet. As a test, can you delete all (or at least some) of the other sheets and then try to update the "bad" sheet?

You mention "other users" - is this a shared workbook (i.e. allowing multiple simultaneous users) ?

Regards,
Brian.

Lindahq,

You offered above to upload your file. Assuming that there's nothing confidential in it, I think this would be a good idea. The file is way too big for Experts Exchange, but you could use one of the free sites (DropBox, SkyDrive, Google Docs etc.) and post the link here.

Thanks,
Brian.

Linda,
I know it's not optimal if the data is refreshed and you constantly need this info, but I find after sheet one is there if you copy paste values, it will dramatically reduce the size of the file.  If you made it 7 sheets before you could complete the project, at least as a snapshot, you could finish all 9 sheets if you copy paste values before moving onto the next sheet.

-SA

I just noticed a typo in my original post.  The file size is 3.94 MB not 394 MB!

Prior to reading your last response, I deleted the offending page, saved the workbook without a hang occurring, and closed the workbook.  I then opened the workbook, and recreated the worksheet.  This time I was able to save the worksheet, BUT with each addition of new section of formulas the paste time and workbook save time increased.  The final paste took over 30 minutes to complete and the save of the workbook took 2 hours.  

I am attaching the workbook.  The offending worksheet is titled "Placing." I was copying the 41 rows of formula from the worksheet labeled "Sheet 1" into sections on Placing.
2011-MASTER-after-changes.xlsx

It should be noted that Excel will never use more the 2GB of memory.  That said, if you can't get your file to work another way I would record a macro that would do the copy paste of the formula for you so it could be a one button update of the "snapshot"

Oops, not SkyDrive - it has an individual file limit way below your file's size.

Sorry, posted above without refreshing from my last post. Thanks for the file, I'll get on to it now.

It doesn't sound like your doing anything wrong, or that there is something broken.  

Trust me, I do a lot of data mining in huge workbooks/databases in Excel, and when you get up there, exactly what your describing occurs.  

I've found the best solution if I can't deal with the slowdown is to just copy paste values to reduce the number of references.  If one or more of the files linked to is in a closed Excel workbook, opening the workbook it is linked to can also reduce the stress on Excel.

Unless you are having problems with other workbooks, I'd say everything is doing what it should.  Your just hitting upper limits of what Excel can do effectively.

Lindahq,

Good news - the problem with this spreadsheet is that there are 160,000+ drawing objects on the Placings sheet.

Bad news - I've spent the last while trying to delete them. Unfortunately everything I've tried leaves the PC running one of its CPU's at 100%. I've canceled each attempt after roughly 10 minutes.

From what you've said, I may simply be too impatient, but I'll try a couple more approaches before I simply let a straightforward delete run for a couple of hours.

Regards,
Brian.

Linda,

Better news - the attached has had the drawing objects removed and is vastly more manageable. I made no other changes. Please give it a go and let me know how it behaves.

Regards,
Brian.
2011-MASTER-after-changes---Clea.xlsx

OMG what a diference!!! It is now a normal spreadsheet.  That explains why the fiel size became so inflated after I added that sheet.

I have no idea how drawing objects would have gotten into that spreadsheet.  It was a new blank spreadsheet that I simply entered formulas into (either manualy or by using copy/paste).

How did you determine the spreadsheet contained drawing objects?

You TOTALLY ROCK!!!!

After I received your message about the drawing objects I deleted the other two worksheets you mentioned.  I too was afraid they would have the same issue.  Actually Sheet1 was just a temporary place to update the formulas before I copied and pasted those into Placing.  RegPt was a copy of Placing that was intended for another use.  So after I deleted it all I had to do was make a copy of the worksheet you fixed.

Thank you so VERY much for the macro.  I have saved it in a very special folder, but hope I never have to use it.

I wish I could give you more than 500 points.  You truly deserve more.  Plus you saved my sanity!!  You assitance was worth far more than what I pay to be a member!  

THANK YOU!!!

PS
Do you ever sleep?<LOL>

Brian's resolution truly deserved more than 500 pts!!  He should be dubbed Excel Lifeguard because he saved my Excel life!

Linda,

Besides the pleasure from your kind words, I get a great kick from killing such an impactful problem - from the little time I spent with the file, I got some idea of the frustration it must have been causing you. Finally, I got to learn more about the internals of spreadsheets.

I should be giving you points!

Many thanks,
Brian.