Solved

Excel 2007 - Error Cannot complete task with available resources

Posted on 2013-06-20
4
1,151 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Copying the data to another workbook solved the issue.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now