File Size oddness

[centprop]

Hi all.

I have what I feel is a really odd problem, hopefully its very straightforward to you guys.

I have a large .xlsm workbook with many sheets, that amongst other things creates various reports based upon a single data set sheet. I copied one of the sheets, and modified it so that the report it creates is slightly different.

I had a little trouble getting it working, with the modified VBA code, but eventually succeeded. I then had to add some stastical information to the bottom of this report, that was calculating percentages based on some of the figures this dynamic report was giving me. This is when I noticed a drop in performance, and it took a while to save. I figured as the data within the report was dynamic, it was causing a slowdown, so I recreated the statistics in another sheet, not using the dynamic report, but the original dataset to produce the figures. I then referenced these new cells from the new report layout, expecting it to be much less intensive, however it didn't help. I then realised that the file size was a massive ~40MB

Just to confirm that the new sheet was the problem I removed it, saved a new version and that now has a file size of 763KB

There is no intensive graphics, or very much going on with this new sheet that I can think of that will be making such a huge difference with the size of it, so what could it be?

What other information can i share with you to help me find out what is going on?

Thanks in advance

Chris

 

[GlennUK]

When you say that you added some information to the bottom of the report ... are you saying that you added it at the very end of the sheet?

 

[TiaXL]

strange thing I noticed, named ranges of data tend to add a lot to the size of a file, strangely.

 

[GlennUK]

strange thing I noticed, named ranges of data tend to add a lot to the size of a file, strangely.

No, that is not something I've noticed, if the ranges are within the used ranges of the sheets. In fact I would recommend using named ranges for a lot of things within Excel ... can you back up your claim with some real examples?

 

[TiaXL]

No, that is not something I've noticed, if the ranges are within the used ranges of the sheets. In fact I would recommend using named ranges for a lot of things within Excel ... can you back up your claim with some real examples?

I have a daily performance report that displays a lot of data, this is produced of the back of a rolling model, the code I use opens this file and jiggs around with the data to get what we need.

We had to add a bit of code to remove the 30 something odd named ranges.

Deleting these defined ranges reduced the filesize from 6mb to 320k

 

[centprop]

There are a few new Named ranges for the new sheet, but its only a fraction of the total number of named ranges. I will look at this though....

When you say that you added some information to the bottom of the report ... are you saying that you added it at the very end of the sheet?

Yes it is right at the bottom of the sheet as it goes. Does this make a difference? Should I try putting the statistics above the dynamic report?

 

[GlennUK]

Yes it is right at the bottom of the sheet as it goes. Does this make a difference? Should I try putting the statistics above the dynamic report?

Yes indeed.

 

[centprop]

Thanks Glenn, that worked a treat.

Why does that make a difference? Is it a bug?

 

[GlennUK]

If you put something in row 1048576 it makes the used range for the sheet huge, that's why. I'm so glad it's not the defined names, as I thought.

 

[centprop]

Ahh! That makes sense.

Interestingly however, this sheet only has 116 rows. The reports "total" line, is one up from the bottom of the sheet in row 115. The dynamic portion of the report is the 100 or so rows above the total row, with the unused rows in the body of the report being automatically hidden.

Thanks again Glenn.