System resources workaround options...

[Oliver Dewar]

Hi All.

I have a large workbook my company uses as a CMS.

We sometimes get the 'Not enough system resources to display completely' error message - especially when a certain sheet is selected either directly or in VBA.

My questions are these:

1. When this error occurs... does excel actually stop calculating? Is there an effect of this error occurring, other than the sheet going 'stuck' until you select it again?

2. If the answer to 1. is 'No, it's purely a display issue' then can I somehow turn error reporting off and tell it to select the sheet until there's no error? (If so how would I do this?)

3. I've seen some solutions suggesting turning calculating onto manual... I don't understand this, but if it means that the sheet will not use its formulas until I turn it back to auto, and that this would stop the error, I can easily incorporate this. Am I right in my definition of this? If so, how would I impliment this in vba code?

Your help will be GREATLY appreciated.

Kind regards,

Oliver

 

[Oliver Dewar]

OK...

Just tested the calculation to manual idea... no help.

But... I've just looked at two of my sheets and they have formulas in the clients data. As we add clients, we add formulas as well... and we end up with thousands of formulas to calculate on sheet selection.

I'm certain that this is what's causing the error.

I'm going to remove all formulas from the data tables and do all calculation of these rows in macros, when we edit the clients record.

That way excel only has to calculate a few formulas per sheet.

I really hope this will handle my issue.

Do you think I'm onto it?

 

[smiler]

hi,

See this thread

http://www.mrexcel.com/forum/showthread.php?t=628624

Also ensure that your 'Used range' is only the rows and columns that you are actually using - goto each worksheet - pres F5 - click the special button - select last cell and click ok

your cursor should go to the last column/last row used if it is lots of blank rows down or lots of blank column to the right then delete those blanks

select all blank rows and right click - select delete - then immediately save
Select all blank columns right click - select delete - then immediately save

 

[Oliver Dewar]

Hi Smiler.

Thanks for your post.

It was very helpful and I've just been through my workbook removing spare cells and clearing needless background colours etc.

I think that will help too.

I'm currently going through all the client data removing functions and replacing them with one off macro sequences.

I've also just realised that there is a fundimental sequence in my workbook that pastes multiple formulas that referrence another sheet. I've got a sneaking suspicion that this could well be the culpret. So I'll have to have a rethink and see if I can replace those formulas with a macro produces equivalent.

Hope all this sorts this issue!

 

[Oliver Dewar]

OK.

So I've just removed a load of formulas and replaced them with vba code. I've also been through the workbook removing unnecissary blank columns. I've also removed a lot of my pretty formatting.

Results?

My workbook has become significantly faster. Great result, but not a fix to the bug.

But wait! I realised that one specific sheet seemed to be causing the error on selection more than the rest. This sheets has only two formulas now so not those... but it does have 2 columns with borders that go all the way to the bottom of the sheet! In my innocence I had simply selected these columns and applied borders all the way down. I've removed those borders and... so far... cannot reproduce the error.

So I think it was those borders!

What would I say to others with this error?

Check large pasting of formulas.

Check large amount of formulas.

Check any large filling of cell backgrounds, cell borders, any large area of formatting generally and remove them.