Excel 2007 - Error Cannot complete task with available resources

Got a support call from an associate who is receiving the message "Excel cannot complete this task with available resources.  Choose less data or close other applications" when trying to Insert Columns.  

I am familiar with this issue from previous versions and have tried the following things to correct it, but to no avail.  If you have any other suggestions it would be greatly appreciated:

Here's what we've tried:

1. Closed all other open applications and re-opened the Excel File.
Pressed Ctrl-End to see where the active area ends.  It ends in MC (2 rows from the bottom of the worksheet).  Tried deleting columns from MC through the end of the worksheet.  Got same error message

2. Tried clearing All formats/data for the cells to the right of column MC - same issue

3. Tried re-saving the file under a different name. Reopened the file and tried again with no success.

3. Went to the Visual Basic Editor for the active sheet ant in the Immediate Windows typed ActiveSheet.UsedRange  to try to reset the Active area.  When we pressed ENTER in the VBE window we got the "Microsoft Excel has Encountered an Error" message and the worksheet closed.

So - I'm stumped.  Any ideas experts?
Who is Participating?
Zack BarresseConnect With a Mentor CEOCommented:
Odd.  Have you tried moving the data to a new workbook (values only) and re-creating it?  We would use this method instead of copying/pasting the workbook itself in case the file has gone bad.  Workbooks can go bad, but not very often.  If this is the case, migrating the data to a new file would be a good test, and may in fact be in order.

Something else you may try before re-creating the wheel, is to select the columns/rows that shouldn't have data and Clear them (values and formats).  In Excel 2007 you can get to it by going to the Home tab, look far-right at the Editing group.  Select the Clear drop down and choose Clear All.  This is native and fast, and shouldn't take near the resources of deleting or inserting.

And of course, you've probably tried this, but a good system reboot is probably in order.  Sometimes people never restart their computer.

Another thing I would try is attempting to replicate this behavior with Excel opened in safe mode.  You can do so (with Excel closed) by holding CTRL key when opening Excel (for more info look here: http://office.microsoft.com/en-us/excel-help/about-office-safe-mode-HP003082393.aspx).

Zack BarresseCEOCommented:
Sounds like there's a lot of data in there.  Column MC is column 341.  And if it's two rows from the bottom of the worksheet in 2007, that's row 1048574, and a total of 37,023,734 cells.  That's a lot of data!  

Maybe take a look at this link:

While it is formatted for Excel 2003, you could make this adjustment to get it to be forward and backwards compatible...

Sub ExcelDiet() 
    Dim j               As Long 
    Dim k               As Long 
    Dim LastRow         As Long 
    Dim LastCol         As Long 
    Dim ColFormula      As Range 
    Dim RowFormula      As Range 
    Dim ColValue        As Range 
    Dim RowValue        As Range 
    Dim Shp             As Shape 
    Dim ws              As Worksheet 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    On Error Resume Next 
    For Each ws In Worksheets 
        With ws 
             'Find the last used cell with a formula and value
             'Search by Columns and Rows
            On Error Resume Next 
            Set ColFormula = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set ColValue = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) 
            Set RowFormula = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            Set RowValue = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) 
            On Error Goto 0 
             'Determine the last column
            If ColFormula Is Nothing Then 
                LastCol = 0 
                LastCol = ColFormula.Column 
            End If 
            If Not ColValue Is Nothing Then 
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column) 
            End If 
             'Determine the last row
            If RowFormula Is Nothing Then 
                LastRow = 0 
                LastRow = RowFormula.Row 
            End If 
            If Not RowValue Is Nothing Then 
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row) 
            End If 
             'Determine if any shapes are beyond the last row and last column
            For Each Shp In .Shapes 
                j = 0 
                k = 0 
                On Error Resume Next 
                j = Shp.TopLeftCell.Row 
                k = Shp.TopLeftCell.Column 
                On Error Goto 0 
                If j > 0 And k > 0 Then 
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height 
                        j = j + 1 
                    If j > LastRow Then 
                        LastRow = j 
                    End If 
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width 
                        k = k + 1 
                    If k > LastCol Then 
                        LastCol = k 
                    End If 
                End If 
            .Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).Delete 
            .Range(.Cells(LastRow + 1, 1), .Cells(.Rows.Count, .Columns.Count)).Delete 
        End With 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
End Sub

Open in new window

See if that helps.

Zack Barresse
jrogersokAuthor Commented:
Thanks, Zack.

It is a lot of data -- not very complex formulas or anything, but definately large.

We have determined that it starts happening when trying to insert columns to the left of EV.  To the right of it we can insert columns.

It won't let us copy/paste or clear formatting on anything to the left of EV as well.  There are formulas (simple 2 cell addition) in EV and we've tried clearing and re-creating them to no avail.  It won't let us delete the column, either.

This is a strange one, experts!
jrogersokAuthor Commented:
Copying the data to another workbook solved the issue.
All Courses

From novice to tech pro — start learning today.