Out of memory when trying to delete all unused rows

[a_driga]

Hi All,

I have read in some source that in order to keep the size of excel file to minimum it is necessary to delet all the unused rows before saving the file.
Somehow the tricks works..

However, when I am trying to incorporate the step to delete all unnecessary rows after running a module using vba, the result is that I am out of memory. When doing it manually, the computer only reminds me of the possible memory run-out but the process itself can still be done.

I am using the following macro code to delete the row:

Please Login or Register  to view this content.

lr1 is the last filled cell in column A, lrend is basicall 1048576 and colend is XFD or 16384

Any suggestion in making the above works and not resulting in crash?

Thanks in advance..

[:) Sixthsense :)]

Instead of Clearcontents use Clear which will remove all the unnecessary clusters.

Please Login or Register  to view this content.

Deletion will delete and create new set of cells/rows which takes more time. Because of deleting the rows it just resets the row height and nothing specail.

So Clear method is enough in your case

[a_driga]

Instead of Clearcontents use Clear which will remove all the unnecessary clusters.

Please Login or Register  to view this content.

Deletion will delete and create new set of cells/rows which takes more time. Because of deleting the rows it just resets the row height and nothing specail.

So Clear method is enough in your case

Actually I commented out the clearcontents since after that method the size of my file still becomes larger.
What I want to achieve is to delete the rows, however deleting the rows causing my calculation to crash due to insufficient memory.

[:) Sixthsense :)]

If you want to delete then there is no need to clear the data, instead you can straight away delete the range by turning off the calculation like the below.

Please Login or Register  to view this content.

[a_driga]

If you want to delete then there is no need to clear the data, instead you can straight away delete the range by turning off the calculation like the below.

Please Login or Register  to view this content.

It appears that using that syntax still required much memory and causing my calculation to stop and showing Run-time error '1004' on that particular syntax

[a_driga]

double posting..