Undesirable massive increase in file size

**1eyedjack** · 04-14-2010, 12:01 PM

This seems to happen in both Office 2002 and Office 2003:
I have a workbook that is 90Kb in size.
I open the workbook, do nothing other than invoke a single specific macro, then save the file and it has increased in size to 1.5 Mb. I can think of no reason why the file size should increase, and I would rather that it did not. Any pointers?

The offending macro DeleteRow() which I invoke is intended to delete certain rows common to the selected range, and then hide all rows below a specific row (these rows were previously hidden but new rows inserted at the bottom of the worksheet become visible by default).

Please Login or Register  to view this content.

The named range "HiddenRows" refers to
=OFFSET(Analysis!AnalRowTemplate,-1,0,65537-ROW(Analysis!AnalRowTemplate),1)

If it helps, I have uploaded the original workbook here:
http://www.keepandshare.com/doc/1857...59-pm-92k?da=y

**royUK** · 04-14-2010, 12:09 PM

I've downloaded the workbook & run the macro with no increase in size.

Try opening a new workbook,move/copy all the sheets & code into the new workbook then see if that's better

**shg** · 04-14-2010, 12:17 PM

Why do this?

Please Login or Register  to view this content.

It extends the usedrange of the sheet to the last row.

**1eyedjack** · 04-14-2010, 12:51 PM

To RoyUK - My profuse apologies. I should have instructed you to ensure that you had selected a row which qualifies for deletion before running the macro. If you do not change the selected cell the macro bombs out without action so no, the file size does not increase. A qualifying row is one near the bottom which has "Description" written in column E.

Anyway, what I have now done is opened the file, ran the macro to ADD rows three times, and saved that file here
http://www.keepandshare.com/doc/1857...1-pm-117k?da=y
So far so good, normal file size about 114Kb

I then selected the newly created 3 rows and ran the delete rows macro, saved the result (file size now 1556 Kb), and uploaded it (zipped) here:
http://www.keepandshare.com/doc/1857...1-pm-190k?da=y

Now, for the benefit of shg, I removed the line

Please Login or Register  to view this content.

before doing all this, and it SEEMED to work in that APART from the increased file size, all is OK. BUT ... I then repeated the exercise (added 3 rows, then deleted the same 3 rows), and the result is saved here:
http://www.keepandshare.com/doc/1857...1-pm-189k?da=y
Note that row 65536 is visible (undesired). Quite why I need to insert that row of code is a mystery to me, but by trial and error it seemed to do the trick.
I have no doubt that the code is inefficient. I am pretty sure that the instruction to CalculateFull should be superfluous, and I may tidy it up when I have (a) got it working properly and (b) stopped it increasing the file size so much.

Thanks for the suggestions so far.