Solved

Excel 2007 - Error Cannot complete task with available resources

Posted on 2013-06-20
4
1,191 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
[X]
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
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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

695 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