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]

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.

Just tested your revised code and it worked. The output confirmed 500,000 rows by 2,500 columns

 

[Mandanpan]

I had this problem even when I tried to delete or copy-paste a few rows right after loading the worksheet with vba macros. I found that this does not happen when I do that after saving the workbook. Now I have added the following code in the Workbook to save automatically when the workbook opens. This may be a trick that may work for you.

Private Sub Workbook_Open()
ActiveWorkbook.Save
End Sub