Excel "Cannot Complete Task With Available Resources" When Trying to Delete Rows

[LadiesAndGentlemen]

Hi everyone,

I have VBA code that attempts to delete an entire row from my worksheet:

Cells(3,1).Select
Selection.EntireRow.Delete

This works fine on small data sets, but on larger data sets it gives me the error message, "Excel cannot complete this task with available resources". This happens even when I try to do the deletion manually (without VBA code). Clearly, the code itself is not the problem.

My document has about 250,000 rows and 2,500 columns. While this is big, it is significantly smaller than Excel's documented limit of 1,048,576 rows and 16,384 columns.

I am using Excel 2007. My computer has 2GB of RAM but even when I try it on a computer which has 8GB of RAM it gives me the same error. If I "ClearContents" instead of "Delete" it works fine. For my purposes, however, deletion is entirely necessary.

Why won't Excel allow me to do this simple thing and what are possible workarounds? Any help would be greatly appreciated.

 

[LadiesAndGentlemen]

I really think you should try what I posted and you wont have to change any code if it works.

Thank you, I am about to try your suggestion. I do not completely understand what to do thought. What do you mean by "update KB2597166"? How do I do this?

 

[MARK858]

If you want to try the tip from Dazzawm then the process is

Open Installed Updates by clicking the Start button

, clicking Control Panel, clicking Programs, and then, under Programs and Features, clicking View installed updates.

Click the update that you want to remove, and then click Uninstall.

If you are prompted for an administrator password or confirmation, type the password or provide confirmation.

 

[LadiesAndGentlemen]

Thank you all for your help! Although I was not able to get any of the suggestions to work, I did find a work-around. Basically I copied the cells I needed rather than delete the ones I didn't need.

Thanks again!

 

[MARK858]

I know you have found a workaround but I am intrigued that you can copy/paste yet not delete, If you get a chance can you try running the code below on your original data an see if it gives you

I now have a document of 525,000 rows and exactly 2,500 columns

Sub ShowUsedRange()
Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer


    ' Select the used range.
    Set sheet = ActiveSheet
    sheet.UsedRange.Select


    ' Display the range's rows and columns.
    row_min = sheet.UsedRange.Row
    row_max = row_min + sheet.UsedRange.Rows.Count - 1
    col_min = sheet.UsedRange.Column
    col_max = col_min + sheet.UsedRange.Columns.Count - 1


    MsgBox "Rows " & row_min & " - " & row_max & vbCrLf & _
           "Columns: " & col_min & " - " & col_max
End Sub

 

[Dazzawm]

Thank you all for your help! Although I was not able to get any of the suggestions to work, I did find a work-around. Basically I copied the cells I needed rather than delete the ones I didn't need.

Thanks again!

Did you do as I said? I am surprised that didnt work.

 

[Latchmaker]

Thank you all for your help! Although I was not able to get any of the suggestions to work, I did find a work-around. Basically I copied the cells I needed rather than delete the ones I didn't need.

Thanks again!

Your Solution confirmed that this was a calculation issue next time turn off automatic calculation before deleting row and then back on after deleting the row.

 

[MARK858]

The calculation is the most likely cause due to the number of formulas but it is not the only possibility. For instance there could be hidden objects outside the expected range or even both combined.

 

[LadiesAndGentlemen]

Did you do as I said? I am surprised that didnt work.

I'm using Excel 2007 rather than 2010 so I wasn't able to use the exact method you suggested. I did, however, do some research and found the corresponding update for 2007 and that did not fix the issue (though there is a chance I messed up somewhere in the process. I also downloaded a hotfix to no avail.

Your Solution confirmed that this was a calculation issue next time turn off automatic calculation before deleting row and then back on after deleting the row.

How would I turn off automatic calculation? The reason I don’t think this was the issue is because my worksheet does not have any formulas. Everything is of type value and there are no cell references unless they are hidden deep under the hood.

I know you have found a workaround but I am intrigued that you can copy/paste yet not delete, If you get a chance can you try running the code below on your original data an see if it gives you

Sub ShowUsedRange()
Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer


    ' Select the used range.
    Set sheet = ActiveSheet
    sheet.UsedRange.Select


    ' Display the range's rows and columns.
    row_min = sheet.UsedRange.Row
    row_max = row_min + sheet.UsedRange.Rows.Count - 1
    col_min = sheet.UsedRange.Column
    col_max = col_min + sheet.UsedRange.Columns.Count - 1


    MsgBox "Rows " & row_min & " - " & row_max & vbCrLf & _
           "Columns: " & col_min & " - " & col_max
End Sub

Just tried this and it actually gives me Run-time error '6' Overflow on the line " row_max = row_min + sheet.UsedRange.Rows.Count - 1." I have run a simple "ActiveSheet.UsedRange.Rows.Count" calculation for rows and columns to confirm that I was indeed dealing with 500,00 rows and 2,500 columns.

 

[LadiesAndGentlemen]

I just tried putting the document into Manual Calculation Mode instead of Automatic and it didn't make a difference. I'm not sure if my manual mode is working though because if I type "=4*5" into a cell and hit enter, it still replaces it with "20". What is manual mode supposed to do?

 

[MARK858]

The reason the code I posted errors out is probably because I did not change the integers to Long. I am on my phone at the moment so can't post code. If you change the integers to Long it should run.