No available resources ???

**Rivers** · 07-19-2010, 06:42 AM

Hi I'm new. When trying to delete or insert a column into a worksheet I'm getting "Excel cannot complete this task with available resources. Choose less data or close other applications". I've tried both and it still will not allow the process. This only appears on one of many workbooks I've created. Totally mystified, any helpful advice would be gratefully received.

**teylyn** · 07-19-2010, 07:19 AM

Hello rivers, welcome to the forum.

How big is the workbook? Are you using complex formulas? Whole column or whole row references in formulas like Vlookup? Array formulae? Sumproduct? Indirect or Offset?

A combination of the above with conditional formatting, using millions of rows and hundreds of columns, has the potential to test the capacity of your machine, because a lot of cells will need to be recalculated when you insert rows or columns.

Maybe you can break the workbook down into smaller parts? Maybe simplify formulae? Or avoid conditional formatting? Or you could switch to manual calculation, insert rows/columns and then switch automatic calculation back on. That might help, but if you're pushing your system's capacity, it probably won't help for long.

cheers

**Rivers** · 07-19-2010, 07:55 AM

Hi teylen, it's a pleasure to be here. Thanks for your swift response. Many cans of worms to be opened with your response, but all I've done with the sheet in question is to work from a template I have created, and other sheets from this template have not suffered from this problem. Can I shrink the size of the worksheet? I certainly do not need anything like the volume of columns or rows available. The formulae are all simple arithmetics (eg c1=a1*b1 etc) with just one column conditionally formatted to show different colours.

**teylyn** · 07-19-2010, 05:14 PM

Could be a case of a corrupt workbook.

Try saving it as a web page, then close the file. Navigate to the html file with the File Open dialog, then open the web page file and save it as an Excel file again. That route sometimes helps repair corrupt workbooks.