by Allen Wyatt
(last updated July 30, 2016)
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.
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:
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:
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Select a range of cells, and one of those cells will always be the starting point for the range. This tip explains how to ...Discover More
When you are entering information in a worksheet, it sure would be handy to have a way to "mask" the information being ...Discover More
When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.