Memory used jumped from 80m to 1.6G from Deleteing method

[Ceeyee]

Please help!

I have following VBA code

Sub DeletefromRow5()

Rows(5).Select
Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp

End Sub


After I run the code my memory usage jump from 51M to 1.6G!! and the program crashed.

Can anyone take a look at my Excel file to see what's happening? I made the file very simple and contain only the above code.

http://dl.dropbox.com/u/9846094/Public/workbook1.xlsm



Thanks a lot!!

 

[doubledaffy]

I did not have problems with the code. Does it crash every time?

Perhaps try deleting the rows without selecting them?

 

[Weaver]

It's a heck of a big 'selection'!

Try the following & see if the situation improves

Sub DeletefromRow5()
    Range("A5", Range("A5").End(xlDown)).entireRow.Delete
End Sub

 

[Ceeyee]

Actually this is just an example for how memory spikes when I am operating this workbook. Doing other operations like formatting 5,6 columns will also show a huge memory spike, while usually it should not happen in other workbooks.

Have you check your memory usage and did it go to 1g level after running the code?

Thanks.

I did not have problems with the code. Does it crash every time?

Perhaps try deleting the rows without selecting them?

 

[Ceeyee]

I appreciate that, but I am trying to find out why using my code would cause such spike in memory usage.

I have used the same code in many other workbooks but never had this problem. Only this one workbook caused the memory usage to spike to 1.6G... !!

Have you tried the button and check the memory usage in Process Manager?

Thanks a lot.

It's a heck of a big 'selection'!

Try the following & see if the situation improves

Sub DeletefromRow5()
    Range("A5", Range("A5").End(xlDown)).entireRow.Delete
End Sub

 

[doubledaffy]

Well I'm stumped. I had the same problem you did when I used your file. The memory usage shot up to around 1.6M and there were program hangs. However, when I started from scratch and did the same thing I didn't have a problem. Perhaps the file was corrupted?

 

[Ceeyee]

Do you mean 1.6g? Yes, this is the problem.

The background: This file is trimmed down from a larger file (a financial model). We noticed recently that the model has this memory problem. Since it's too big to be posted here so I deleted all the other things and left the most basic data and posted it here for help.

We need to fix this file because we need to find out what the problem is so that we can fix the bigger model, which is where this small file comes from.

Can anyone help? Thanks a lot.

Well I'm stumped. I had the same problem you did when I used your file. The memory usage shot up to around 1.6M and there were program hangs. However, when I started from scratch and did the same thing I didn't have a problem. Perhaps the file was corrupted?

 

[doubledaffy]

Perhaps your module is corrupted. I deleted the module that was in your file and made a new one from scratch and no longer had the problem.

 

[Ceeyee]

I deleted the Module, but still have the problem even when trying to delete one column manually.

Can you take a look at this file which is macro free?

http://dl.dropbox.com/u/9846094/Public/workbook2.xlsm

Thanks.

 

[doubledaffy]

I get this error message when I try to delete the entire row.
-- removed inline image ---
"The operation you are about to perform affects a large number of cells and may take a significant amount of time to complete. Are you sure you want to continue?"

However, if I just delete the requested cells manually, it doesn't have a problem. You've got something strange going on with the other cells in the worksheet. I don't know what would have caused it, but you might want to try starting as fresh as you can.