Not Enough Resources to Delete Rows and Columns

Written by Allen Wyatt (last updated March 25, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365


1

Harvey has a large Excel worksheet, cuurently 700 columns and 1000 rows. It has formulas in about 600 of those columns, some of which refer to other worksheets held on the server. In total there are about 420,000 formulas in the worksheet. He needs to delete some rows and columns, but Excel gives an error message that he does not have enough resources. Harvey has Googled the problem and tried all the suggested solutions but still has the problem. He wonders if there are any thoughts as to how he can remove the columns and rows.

Tracking down and correcting this type of problem can be frustrating, as it could be caused by any number of things. (Microsoft isn't that great on providing helpful information in some of its error messages; this particular message is a prime example.) There are a few things you can try that are quite easy.

  • Close all workbooks you have open except for the one in which you are trying to do the deletions.
  • Turn off automatic calculation before you attempt to do the deletions.
  • Delete rows and columns one row and column at a time, rather than deleting large numbers of rows and columns at once.
  • Clear the contents of the columns and rows instead of deleting them. Save after the clearing and then try to delete the previously cleared columns and rows.
  • Restart your system, open Excel before anything else, and try to do the deletion.
  • Try opening the workbook on a different machine or in a later version of Excel.
  • Use Save As to store the workbook on a local drive, then open the new version of the workbook and do the deletions.

If these suggestioins don't work, then you'll need to continue with the trial-and-error process. You can find some good information on possible solutions on this page:

https://www.add-ins.com/support/out-of-memory-or-not-enough-resource-problem-with-microsoft-excel.htm

The advice on the page is particularly good if you suspect that the resource issue is related to a misbehaving add-in.

You might also check to see if you are pushing the limits imposed on Excel by Microsoft. You can find information on these limits here:

https://support.office.com/en-us/article/Data-Model-specification-and-limits-19aa79f8-e6e8-45a8-9be2-b58778fd68ef

Note that this page also provides access to a "workbook size optimizer." You might find this tool helpful in reducing the size of your workbook and thereby freeing up resources.

Finally, it could be that your workbook is slightly corrupted. (I've had this happen to me with much smaller workbooks.) The best solution in this case is to copy the data from the worksheet to a new workbook. Don't copy or move entire worksheets; you need to copy the actual data. You might even try to save your workbook as a CSV file and then load the CSV file into a different workbook. You will lose formatting in this manner, but everything else should come across just fine.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (407) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Hyperlinks Not Found

When creating hyperlinks in a document, it is important to remember the difference between absolute and relative ...

Discover More

Jumping to the End of Page after Enter

Imagine you start typing in a new document, and when you press the Enter key the cursor jumps a huge distance to the ...

Discover More

Changing to the Right Thesaurus

Ever want Word to display a thesaurus for your country's version of English? This tip explains how to find the different ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Entering Dates in Excel

When you type information into a cell, Excel tries to figure out what type of information you are entering. If Excel can ...

Discover More

Defining Shortcut Keys for Symbols

Do you need to use symbols frequently in your Excel data? The common way to insert them is by using the Symbol dialog ...

Discover More

Importing a Subset of Records

If you only want to import a portion of whatever records are in a text file, Excel provides a number of ways you can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 8?

2023-03-25 17:25:18

Tomek

A problem in saving as CSV is that you lose all the formulas. What is saved are results of these formulas saved as values.

Instead, you may try one of other formats that Excel recognizes; ones that come to mind are:
.xlsb - Excel binary workbook,
.xml - XML Spreadshee 2003 (not just XML data - Excel will likely reject such attempt),
.ods - Open Document Spreadsheet.

You can experiment with other formats including old .xls. Depending on how complex your spreadsheet is and whether the formulas used are specific to the newer version of Excel, you may lose some functionality and possibly formatting. If it works however, it may remove some of the demand on resources.

As for lost formatting, you can restore it from the original file to the fixed one by using Format Painter.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.