Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2007 - Error Cannot complete task with available resources

Posted on 2013-06-20
4
Medium Priority
?
1,235 Views
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?
0
Comment
Question by:jrogersok
  • 2
  • 2
4 Comments
 
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:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

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 
            Else 
                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 
            Else 
                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 
                    Loop 
                    If j > LastRow Then 
                        LastRow = j 
                    End If 
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width 
                        k = k + 1 
                    Loop 
                    If k > LastCol Then 
                        LastCol = k 
                    End If 
                End If 
            Next 
             
            .Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).Delete 
            .Range(.Cells(LastRow + 1, 1), .Cells(.Rows.Count, .Columns.Count)).Delete 
        End With 
    Next 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
End Sub

Open in new window


See if that helps.

HTH
Regards,
Zack Barresse
0
 
LVL 2

Author Comment

by:jrogersok
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!
0
 
LVL 14

Accepted Solution

by:
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).

HTH
0
 
LVL 2

Author Closing Comment

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

810 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