Excel 2010 Error Message "Cannot Complete task insuffucient Resources, close other applications"

[Johnny Thunder]

Hello All,


I am using Excel 2010 (Not Beta anymore) and I am getting an error message whenever I highlight multiple tabs (more than 6) and try to do something like Delete all contents in tabs?


Any ideas why this is happening? The file isn't even that large? And I do multiple SAVE's while I am working so the file doesn't get held up?

I'm guessing there is a setting that I can change to allow Excel to maximize performance just not sure where to do this?

 

[Johnny Thunder]

Anyone? Please help?

 

[xenou]

1,048,576 rows * 16,384 column * 6 tabs = 103 Billion cells. I'm on the side of the application on this one - too much to handle now!

 

[Johnny Thunder]

Why is this an issue now? It never was on 2003 or 2007?

 

[Trebor76]

In comparsion the number of cells in each 2003 tab (16,777,216) is far less (1/1000) than that of 2007 (17,179,869,184) and beyond.

 

[xenou]

Its hard to test (I tried filling some entire columns of data with a value, and populating across columns .... after only 50 columns I was at 130 MB and unable to delete the whole sheet anymore either without at least a warning (insufficient memory to undo the operation). I gave up at that point - it seemed clear enough that multiplying this out by hundreds of columns would get out of hand. For what its worth, I was completely unable to select the entire sheet and enter a value into all cells at once, much less 6. So I wrote a macro to fill the sheet column by column - that was looking to take a a good half hour to complete. So I don't know if I could do a delete all at once (or even how big that file would be ... probably huge, who knows. What's 130 MB * 300 get to?

 

[Johnny Thunder]

Well,

Going through and working with worksheets that have alot of data and running my macro's all I do now when I get the error message is hit okay about 2-3 times and the macro does it's thing? I guess I just need to be more patient?

Well hopefully we are not the only one's to be discussing this problem, Microsoft needs to make a solution and roll it out, For Free!

 

[Trebor76]

I know M$ greatly increased the size of the calculation engine for Excel 2007 and beyond but if your machine can't handle the load there's nothing (that I can of) that M$ can do about it.

I suppose if you have masses amount of data then perhaps you should give some consideration to switching to Access as, unlike Excel, is a true database application (I appreciate it maybe a steep learning curve though).

 

[xenou]

My personal opinion is that we have billions of cells too many! I am seeing some indication that Excel could be used in some pretty high-powered settings and its nice to know you won't run out of rows in some cases, but for the average day's work I'd say if you're actually using all the rows or columns on a spreadsheet then its probably too much Also my boss can actually tell me to make a column for every day of the year now and that's annoying. Note: sorry to be curmudgeonly - I suppose I needed to get that off my chest. You may find that there's ways to improve performance by tweaking the design/setup/structure of your workbooks. If you're really serious you can do some reading up at http://www.decisionmodels.com/index.htm which really gets into the nitty gritty on Excel performance (disclaimer: I don't have any affiliation with the site or its products But I know they are well respected).

 

[thuxley]

Can I ask if you are using the default file extension or have you saved as 97-2003 compatible with just .xls extension?

I've generally found that by setting the file compatibility to the older format that 2007 and higher crash less often, as this also changes the number of itterations even though there is the same number of rows and columns with the native version you are using as a default.