+ Reply to Thread
Results 1 to 16 of 16

Large Operation, then Excel crashes

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Large Operation, then Excel crashes

    Hi guys,

    I'm trying to insert a new row into this spreadsheet. But it gives me an error message saying "The operation you are about to perform affects a large number of cells and may take a significant amount of time to complete. Are you sure you want to continue?"

    If I press ok, then gives me an X error with "Excel cannot complete this task with available resources. Choose less data or close other applications.

    This is a bit silly as I only have like 14,000 rows with maybe 30 or less columns. This usually does not give me any problem.

    Why is this doing this? And why is Excel taking up so much of my RAM? (at almost 1.74GB). Also, the spreadsheet thinks I am using up to XGF columns. I'm trying to delete them but because of the error above, cannot do so.

    Thanks for your help guys.

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Large Operation, then Excel crashes

    Hi,

    A few tricks to hopefully solve your problem.

    1) Restart your computer.

    2) Open Excel >> Turn calculations to manual >> delete all unused rows and columns >> Save file >> Close Excel

    3) Open Excel >> Turn calculation to automatic (as before).

    See if this helps.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Re: Large Operation, then Excel crashes

    Quote Originally Posted by abousetta View Post
    Hi,

    A few tricks to hopefully solve your problem.

    1) Restart your computer.

    2) Open Excel >> Turn calculations to manual >> delete all unused rows and columns >> Save file >> Close Excel

    3) Open Excel >> Turn calculation to automatic (as before).

    See if this helps.

    abousetta
    Hi there,

    Thanks for the reply. I'm stuck on #2. Still won't let me delete with the same error message...

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Large Operation, then Excel crashes

    OK. Once you open Excel (not the file of interest) >> turn calculations to manual then open the file of interest.

    abousetta

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Large Operation, then Excel crashes

    Hit end/home and see where you end up. It its out in the middle of nowhere, start deleting rows and columns to get back to the last "correct" row/column cell

    Or, open a new file/workbook and try to copy just that data to the new file.

    It that still doesnt work, would it be possible to upload the file? (as long as it doesnt contain sensitive data)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Large Operation, then Excel crashes

    Go here http://www.microsoft.com/en-us/downl....aspx?id=21649 and download and install the Excess Format Cleaner.

    This resets the last cell of the active area cleaning out everything that is not in the active area of your worksheet. Go to the last row of your data click in column A 1 row past the bottom of your data and then run the add-in.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Re: Large Operation, then Excel crashes

    Quote Originally Posted by abousetta View Post
    OK. Once you open Excel (not the file of interest) >> turn calculations to manual then open the file of interest.

    abousetta
    Hi, I tried your method, still gives me the same error. I opened a blank spreadsheet (Book1), opened the file of interest, calculation is still set to Manual like you mentioned. However, won't let me adjust.

  8. #8
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Re: Large Operation, then Excel crashes

    Quote Originally Posted by FDibbins View Post
    Hit end/home and see where you end up. It its out in the middle of nowhere, start deleting rows and columns to get back to the last "correct" row/column cell

    Or, open a new file/workbook and try to copy just that data to the new file.

    It that still doesnt work, would it be possible to upload the file? (as long as it doesnt contain sensitive data)
    Hi,

    Sorry, the file contains confidential information, thus I cannot upload it here.

    However, I suspect that the overwhelming number of calculations in the file may be causing this issue. There are literally hundred thousands of cells filled with long formulas.

  9. #9
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Re: Large Operation, then Excel crashes

    Quote Originally Posted by newdoverman View Post
    Go here http://www.microsoft.com/en-us/downl....aspx?id=21649 and download and install the Excess Format Cleaner.

    This resets the last cell of the active area cleaning out everything that is not in the active area of your worksheet. Go to the last row of your data click in column A 1 row past the bottom of your data and then run the add-in.
    Hi,

    Thanks, however, it works and does not work at the same time.

    It works on some spreadsheets, but it ended up deleting one of the entire worksheet. Not sure if I want to use this as I do not know what the logic behind is.

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Large Operation, then Excel crashes

    Try copying the offending worksheet to a new workbook, or a new sheet in the same workbook.
    Then add your rows.

  11. #11
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Large Operation, then Excel crashes

    Are you also using volatile functions ?
    Try opening your file from another one using following macro? ( replace file name as needed)

    Please Login or Register  to view this content.
    Last edited by Pepe Le Mokko; 07-16-2013 at 10:03 AM.

  12. #12
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Large Operation, then Excel crashes

    Unwanted duplicate post - Forum trouble
    Last edited by Pepe Le Mokko; 07-16-2013 at 10:02 AM.

  13. #13
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Re: Large Operation, then Excel crashes

    Quote Originally Posted by Marcol View Post
    Try copying the offending worksheet to a new workbook, or a new sheet in the same workbook.
    Then add your rows.
    I tried, it works but I am trying to stay away from this method as there are way too may drop-down list, pivots and other formulas dependent on this spreadsheet. I don't know how to re-create this spreadsheet withot breaking any links to other spreadsheets.

  14. #14
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Re: Large Operation, then Excel crashes

    Quote Originally Posted by Pepe Le Mokko View Post
    Unwanted duplicate post - Forum trouble
    Hi,

    How do I reference the file? I copied the path in the bracket but it tells me compile error.

  15. #15
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Large Operation, then Excel crashes

    Are you referring to post #11?

  16. #16
    Registered User
    Join Date
    11-15-2012
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 365
    Posts
    49

    Re: Large Operation, then Excel crashes

    Quote Originally Posted by Pepe Le Mokko View Post
    Are you referring to post #11?
    Yes sir, sorry for the confusion.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 10-11-2012, 11:30 AM
  2. [SOLVED] Excel crashes while macro processes large text file
    By RitaG in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-05-2012, 10:34 AM
  3. large file uses massive memory - crashes
    By lotsbg in forum Excel General
    Replies: 1
    Last Post: 05-17-2012, 02:17 AM
  4. minus operation in excel
    By narang.mohit in forum Excel General
    Replies: 2
    Last Post: 03-28-2009, 01:53 PM
  5. excel file operation
    By bart76 in forum Excel General
    Replies: 3
    Last Post: 09-25-2008, 08:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1