Excel 2007 - Error Cannot complete task with available resources

Posted on 2013-06-20
Medium Priority
Last Modified: 2013-08-01
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?
Question by:jrogersok
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 14

Expert Comment

by:Zack Barresse
ID: 39264595
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

Author Comment

ID: 39267068
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!
LVL 14

Accepted Solution

Zack Barresse earned 2000 total points
ID: 39276482
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).


Author Closing Comment

ID: 39373563
Copying the data to another workbook solved the issue.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question