Excel 2010 crashes with very, very large spreadsheets


  • When I get the problem:

    All the time with very large worksheets.

    I have a 155 Mb Excel 2010 worksheet file. After executing it I copy and past the values into a spread sheet to a new file.


    What problem I see:

    A message pops up saying that there is not enough resources to display. The Excel icons in the task bar goes black. Click OK and the next message pops up saying that undo will be disabled. Sometimes I can save the new file sometime I cannot. When I cannot it is becasue the file tab in the ribbon will not respond.


    What I have trouble shooted:

    Sometime I can close Excel and start again, but most times I have to reboot the PC for Excel to work correctly.

    The max memeory I've see it use is about 34%


    What I have:

    PC: HP Pavilion Elite HPE running 6 dual cores (with Excel)

    Processor: Intel(R) Core(TM) i7 CPU x980 @ 3.33GHz

    Installed RAM: 12.0GB

    System Type: Windows 7 Professional, 64-bit Operating System 


    I am willing to provide MS with a copy of this worksheet for trouble shooting purposes.


    Please advise.


    • Moved by Calvin_Gao Friday, 2 September, 2011 8:30 AM (From:Excel for Developers)
    Sunday, 28 August, 2011 12:44 AM

All replies

  • If you are using Excel 2010 32-bit (which is the default installation) Excel is limited to 2GB or less becauise of memory fragmentation.

    If you install Excel 2010 64-bit it might solve the problem.

    Charles Excel MVP The Excel Calculation Site
    Sunday, 28 August, 2011 9:33 AM
  • If not, have you tried using a VBA macro to do the copy using an array reference to a worksheet range ?
    You could do it quickly in "chunks" of 65,000 rows.
    So for 650,000 rows it would take 10 iterations.
    However, it would be pretty fast.

    This would eliminate having to use the office clipboard which may be the problem here.

    Sunday, 28 August, 2011 8:53 PM
  • Thanks, I did not realize it but I was using the 32-bit version. Will let you know how it goes in early Oct - I have a conference in Orlando, FL, and dare not make chnages until after the conference.




    Tuesday, 30 August, 2011 4:33 PM
  • Thanks, but I try not to use macros, but will do if charles's solution does not work.




    Tuesday, 30 August, 2011 5:01 PM
  • Hi solomon,

    How about the issue now? Have you solved it?

    Addition, this forum is devoted to question involves Excel coding, for your question, I would like to recommend you to post in Excel Answer forum for better response.

    Thanks for your understanding and support.

    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, 2 September, 2011 8:30 AM