Increasing file size...

[Tony]

I created a macro that is used to update/add information to another separate
file. This saved file was getting to be rather large so I opened a new
workbook and copy/pasted each sheet into the new file. This reduced the size
by quite a bit, but once I ran the macro on this file again it jumped from
about 6.5M to 17.5M. Is there a way that I can prevent this? Maybe some kind
of code I can put into the macro that will get rid of what ever it is that is
increasing the file size? Thank you.

 

[Fredrik Wahlgren]

I created a macro that is used to update/add information to another separate
file. This saved file was getting to be rather large so I opened a new
workbook and copy/pasted each sheet into the new file. This reduced the size
by quite a bit, but once I ran the macro on this file again it jumped from
about 6.5M to 17.5M. Is there a way that I can prevent this? Maybe some kind
of code I can put into the macro that will get rid of what ever it is that is
increasing the file size? Thank you.

http://archive.baarns.com/excel/develop/vbaperfm.asp

 

[Norman Jones]

Hi Tony,

See Debra Dalgeish's notes at:

http://www.contextures.com/xlfaqApp.html#Unused

However, I would be more interested in amending the code to pre-empt the
file bloat.

Without any code it is difficult to comment further.

 

[Jim Thomlinson]

The best plan of acction is to not increase the file size to begin with. If
you take a look at the sheets the right hand scroll bar on one or all of the
sheets will allow you to scroll all the way down to row 65,535 (my best guess
at what is going on). Somehow your code is doing something to initialize all
fo these cells. They will be blank but they will have been initialized and
stored in memory. To clean this up just delete (not clear the contents) of
these empty cells and resave the file. The size should go down substantially.
If this is the case then find the code that causes these cell to become
initailized. One thing to look for would be code that reference row 65,535...
The offending line or lines of code will also take longer to execute (a
second or two) ...

HTH