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.

 

[taurean]

Could you let us know what is the criteria for deletion? There are many ways the code can be written so something will surely work for you. Especially your computer specs do not seem to be a problem at the outset.

 

[LadiesAndGentlemen]

Thanks for your reply. I am working with a chart that has different towns listed vertically in column A and a bunch of data about each of these towns in the subsequent columns. There are some blank rows in my data which I need to delete. To do this without having to iterate through all entries I run a filter which isolates the blank rows and then iterate through that smaller list, deleting each row as I go down. When I am done, I take off the filter and I am left with a chart that does not have blank rows.

As I stated in my previous post, the way I am deleting rows is by selecting a cell in the row and calling "EntireRow.Delete". Even when I manually right click a row and try to delete it, I get the same error.

 

[Mahesh_P]

Hi
this issue is most of related with RAM of Machine but on 8GB RAM u r getting same error its strange.
i suggest.
Save u r file in Excel Binary Format (xlsb)
use below code

Sub Mtest()
'On Error Resume Next
'Application.DisplayAlerts = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual


'Your code


'Application.DisplayAlerts = True: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
'On Error GoTo 0
End Sub

 

[Latchmaker]

Set your Calculations to Manual I am sure it is because of too many formulas that are having to be moved up one cell.

Try this VBA Code
Application.Calculation = xlManual
Rows("3:3").Select
Selection.Delete Shift:=xlUp
Application.Calculation = xlAutomatic
Calculate

 

[MARK858]

Have you checked the usedrange to make sure it is what you expect?

 

[Mindpsyche]

What about this method? In the column where you check for blank rows, use find special cells - > blanks - > selection.entirerow.delete

Sub Macro1()
    Range("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    With Selection
        .EntireRow.Delete
    End With
End Sub

Also, check if your clipboard is full? Ctrl+C+C

 

[Dazzawm]

I had the same problem, look here for the answer. http://www.mrexcel.com/forum/showth...-Choose-less-data-or-close-other-applications

 

[LadiesAndGentlemen]

Thanks so much for everyone's responses. I am testing all these suggestions out one at a time. Unfortunately, Mahesh_P's suggestion was not successful.

Hi
this issue is most of related with RAM of Machine but on 8GB RAM u r getting same error its strange.
i suggest.
Save u r file in Excel Binary Format (xlsb)
use below code

Sub Mtest()
'On Error Resume Next
'Application.DisplayAlerts = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual


'Your code


'Application.DisplayAlerts = True: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
'On Error GoTo 0
End Sub

I simplified my code to give a better idea of what I am now dealing with and what exactly is not working. To clarify, I now have a document of 525,000 rows and exactly 2,500 columns. When I tried running

Sub Special()

On Error Resume Next
Application.DisplayAlerts = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual

Cells(2, 1).EntireRow.Delete

Application.DisplayAlerts = True: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
On Error GoTo 0

End Sub

As Mashesh_P suggested, I got the same "Cannot complete task.." error.

 

[Dazzawm]

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