Excel 365 crashing, insufficient memory?

Question

_{Tuesday, October 16, 2018 3:11 PM}

Hi guys,

I'm having a hard time with Excel crashing while running a VBA add-in. It runs fine when I'm working with a smaller quantity of data, but the data size changes daily.

I do often get "There isn't enough memory to complete this action" messages when I run the "refresh all" command, however I have been watching the resource monitor and it's not getting anywhere near full memory usage. Here's my system specs:

HP Z820 CAD workstation, Intel Xeon CPU ES-2637 V2 @ 3.5GHz
500GB SSD, 268GB free
Windows 7 Professional 64-bit
64GB RAM
Excel 365 64bit

The spreadsheet has a few data worksheets and a series of 20 worksheets with pivot tables. Usually the crashes happen when iterating through the pivot tables, where it is working cell-by-cell to adjust cell text and fill formatting based on the data in the data worksheets.

I have eliminated all COM add-ins, shut down every other application (no email, browsers, other office applications, etc), and cleaned out the temp directory to no avail.

I really don't think I'm running too little RAM, but it is apparent that Excel is not making use of the vast quantities of RAM available on this machine. What can I do to keep things running?

All replies (4)

_{Wednesday, October 17, 2018 10:33 AM}

Hi,

I have noticed your question. I am actively doing analysis. I will reply you after completing the analysis.

Best Regards,
Herb Gu

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams

_{Tuesday, October 23, 2018 7:25 AM}

Hi,

Sorry for my delay. From your description, I know that you refresh all PivotTables in a spreadsheet and Excel crashes, prompting: “There isn't enough memory to complete this action ”. Is my understanding correct? Is this problem only present on this spreadsheet? No similar problems occurred when using Excel to process other spreadsheets, right?

First you can try to answer the following questions to help me analyze the problem better for you:

>> I have eliminated all COM add-ins

I know that you have eliminated all COM add-ins. How did you do that before? You can refer to Add or remove add-ins in Excel to remove this add-in to see if it works.

This issue may be related to the current workbook.

>> The spreadsheet has a few data worksheets and a series of 20 worksheets with pivot tables. 

Did you use any Macros(or VBA) or Controls in this workbook? For the data source in the spreadsheet, is the data also from external data source, such as imported databases, external data references, and so on?  If the workbook references external data, what type of data is there, where is the data source, how to reference and import it? Is there an error in the reference (import) process?

You can do the following test to see if the problem can be solved, or tell me the test results so that I can further troubleshoot this problem for you:

Note: You must Back up this workbook before testing to avoid important data loss.

Let's try to treat this document as a corrupted document, you can follow Repair a corrupted workbook to repair it. Will this problem still occur after repairing?

Follow Locate and reset the last cell on a worksheet to avoid possible issues.

Copy this workbook file to other devices, use Excel to open and perform the same operation, will this problem still occur?

Try to create a new blank workbook, copy the data in the problematic workbook, create multiple pivot tables to refresh, and will this problem occur?

Go to the Advanced Tab in Excel Options ( File > Options), enable Ignore other applications that use Dynamic Data Exchange (DDE) to check if it will solve this issue.

Make sure you have all latest video/graphics drivers installed. Also update Office by going to FILE > Account > Office Update > Update Now. You may also uncheck 'Disable hardware graphics acceleration' from File> Option>Advanced >Display and see if it helps.

Let's refer to the following thread which discusses about the similar issue and see if it helps: Excel(There isn't enough memory to complete this action). Reply with the result and I'll be happy to help you further.

If you have any question or update, please feel free to let me know.

Best Regards,

Herb Gu

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams

_{Tuesday, October 30, 2018 1:26 AM}

Hi,

I am checking the status of this issue. Do you have any update for it?
If my reply is helpful to this question, please remember to mark it as answer. Your action would be helpful to other users who encounter the same issue and read this thread. Thanks for your understanding.

Best Regards,

Herb

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams

_{Tuesday, November 6, 2018 1:12 AM}

Hi,

I am checking the status of this issue. Do you have any update for it?

This thread has been around for a long time. Please remember to mark the replies as answers if they helped.  Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding! If you have any questions, or updates, please feel free to let me know.

Best Regards,
Herb Gu

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams