Excel worksheet not responding

64 or 32 bit version of Excel?

For really huge and complex spreadsheets, you might have to migrate to the 64 bit version. Most users are still happy with the 32 bit one, however most users don't have 350MB spreadsheets. A lot of useful addins are also 32 bit only, so there may be some problems integrating with them.

Having 64Gb of RAM is pretty pointless with the 32 bit version of Windows, it can only utilize a bit under 2Gb.

More here:

https://support.office.com/en-gb/article/choose-between-the-64-bit-or-32-bit-version-of-office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261?ui=en-US&rs=en-GB&ad=GB

Mal, I am using a 64 bit OS and Windows Pro 64 bit and 64 bit Office Pro

It might help if you saved the workbook as an xlsb file.

You could Google "Excel not responding" and follow the suggestions from Microsoft.
But I don't think it will help.

I have seen it on much smaller workbooks, a few MB, with a lot of formulas, so I guess it is the complexity of the workbook that matter, not the size.
Typically when applying or removing a filter option.

I think "Not responding" means that Windows is not getting a reply from Excel, because Excel is busy with something, not that Excel has stopped working.
Looking in the Task Manager Excel is still using CPU, perhaps 30%, and after a while when recalculation starts, Excel respond again and CPU goes to almost 100% for Excel.
So I just wait for Excel to finish.

Then I investigate the formulas and change if possible.
Also formulas in conditional formatting.
Meaning limiting ranges, not using entire columns, reuse values if subsequent formulas reference the same, adding helper columns to use Index when several columns use the same data row, and similar to reduce the number of intensive calculations.

And for the crash risk, frequently save the workbook, and have a copy or backup to use if something happens to the workbook.

I've been down this road a few times and know and tried most of the memory saving fixes. The following analogy best describes the problem.
A man who has gained weight over time tries to fit into a suit he hasn't wore for a long time. He performs all the tricks like breathing in while pulling up his pants. Avoids fastening coat buttons and wears his shirt over his waist to hide his bulging belly.... The answer he goes out and buys a suit that fits..... BTW that man was me :)
It seems I need a more efficient way of handling the hundreds of memory sapping formulas on a workbook that links direct to a datafeed service. The data is not a problem as there are less than 400 rows and 90 columns involved.
I'm disappointed that Microsoft don't have a more powerful version of Excel that can accommodate several GB's so large volume users don't have to do tummy tucking. I need an alternative method if there is one out there.

Did you try my xlsB suggestion?

Hi Martin,
Nice to see you on here. I haven't as yet. How would it affect the formulas if I saved the wbook in xlsb ?
Most of the memory is taken up using complex formulas.

Please ignore the comment I made earlier starting with .........
I have discovered the file size was 247MB. I have copied the sheets from the workbook to a blank one and the workbook size is now 14MB

That was another file I was getting confused with. I tried deleting the comment but it didn't work.

It will have no effect on formulas. If you have concerns you could save it under a completely new file name.

BTW deleted comments do look deleted until the page is refreshed.

Oh yes Martin, the comment has now gone.
I'll now save one of the workbooks to xlsb and see what happens.

Hi Martin, Here are the results below.
xlsx   open 1.34 mins   calc 15 sec  save 45 sec
xlsb  open   .44 sec       calc 18 sec  save  25 sec
overall yes, xlsb is faster
This may save me some time overall however it remains to be
seen if the not responding problem is resolved

My VBA skills are almost zero but I'll play around with it and see if I can get it to run.
Thanks Martin

Thanks guys for all your help. Plenty of food for thought and will sharpen my VBA skills

Ian

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Thanks Martin, I will.

I just noticed that this is wrong. (The wrong event)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate = xlCalculationAutomatic

End Sub

Select allOpen in new window

I should be
Private Sub Worksheet_Activate()
Application.Calculate = xlCalculationAutomatic
End Sub

Thanks Martin!