Not Enough Resources to Delete Rows and Columns

by Allen Wyatt
(last updated July 30, 2016)

6

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.

  • 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, and 2016.

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

Finding the Smallest Even Value

When processing data in a worksheet, you may have a need to know what the smallest (lowest) even value in a range is. You may ...

Discover More

Backing Up Your AutoCorrect Entries

Want to protect the information that you may be stored in your AutoCorrect entries? Just find a special type of file on your ...

Discover More

Discovering the RGB Value of a Custom Text Color

Word allows you to easily change the color of the text in your document. If you get a document from someone else, you may ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (RIBBON)

Quickly Entering Data

Excel includes a handy shortcut for entering data that is similar to whatever you entered in the cell above your entry point. ...

Discover More

Entering the Current Time

Need to enter the current time into a cell? It's easy to do using this keyboard shortcut. The shortcut is a handy one to know ...

Discover More

Setting a Length Limit on Cells

Limiting what can be entered in a cell can be an important part of developing a worksheet that other people use. Here are a ...

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 for this tip:

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. 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 seven minus 6?

2016-12-27 22:31:01

jsydrah

One thing I finally found was causing this issue for me for months was that if your workbook performs web queries or data connections, these can be stored by Excel and accumulate over time leading what should be a small workbook to use GBs of memory and throw the available resources error. I finally found the solution to clean up this problem at https://youtu.be/ZsIDLnPf09U. This completely solved my problem, bringing my workbook's memory usage down from GBs to MBs.


2016-08-03 07:19:08

Dick Nell

I recognize this problem. What also can help is first do some kind of sorting so the deleted data is joined in the sheet. If you expect this resource message to appear you may also add a column where you mark the rows that should be deleted, next you sort on this column so the deleted rows are at the very end of the data. Then deleting the rows will usually work.


2016-08-01 07:52:33

Allen Farnworth

Another cause of resource problems can be running more than one Workbook. Each workbook you open which contains macros or VBA programming grabs resources and these resources are not released when the workbook is closed. Close Excel, restart and then re-open only the workbook you are having problems with. You will now have more resources. Whether you will have enough resources or not remains to be seen.


2016-08-01 04:33:33

Fligny

You can also know the Used Range in a worksheet without VBA, simply using Ctrl-End key combination. this Range is refreshed any time the workbook is saved.
If no data is present at this point, the resaon is you used Clear instead of Delete. Then you should Delete those unused rows or columns that make the file bigger and slower to use.


2016-08-01 03:57:27

Brian Rowe

I have had numerous situations where it appears that you are only using a few rows and/or columns. Depending upon the way rows (or cell content) has been deleted, Excel still counts these rows as being used, therefore slowing down calculations and actions. In the immediate window, within the VBA editor you can confirm the number of rows / columns used with the following code:

?activesheet.usedrange.columns.count

?activesheet.usedrange.rows.count

This will at least help clarify your issue...


2016-07-30 05:18:32

Petros

Have you ever encountered a situation where Excel cannot complete your task with available resources in your computer?

If the Undo history is disabled, no memory will be allocated for it. Excel can use the maximum memory available in your computer, when Undo levels are set to zero.

The Undo setting cannot be changed from Excel options. The Microsoft article 211922 describes how to modify the Excel's undo levels using the registry editor. Modifying the registry manually may cause serious problems that may require Windows to be re-installed in extreme cases.

Our free Excel Addin lets users change or disable Undo levels without the risk of registry corruption.

http://www.spreadsheet1.com/excel-options-addin.html


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.

Links and Sharing
Share