File Size Inexplicably Inflated - 30 megs

[Mark]

I am currently building a workbook with multiple pages which tie data
between various tabs within the same workbook. There are a number of
interations that the data is going through; however, there are no pivot
tables, no references to other workbooks, and 2 very small macros. However,
the file size recently jumped from 5meg to 10meg.. and then again from 10meg
to 30meg.

Any ideas on what could have caused the problem?

Currently, I am reworking some of my equations early on in the data
processing, so there are a number of #ref errors on later pages. Could this
be why?

[Otto Moehrbach]

Mark
There are several causes for a file getting bigger than one thinks it
should.
Let's start with the most common. This is that he "Used Range" in the
sheets is much larger than you think it actually is. For instance, say that
the last cell of your data on some particular sheet is actually R500. But
Excel thinks the last cell is CA35000. That will cause the file size to be
bigger than you expect.
Do this with every sheet in your file. Manually find and note the last
cell of your data. Now do Ctrl - End. This will take you to the cell that
Excel thinks is the last cell of your data. If these two "Used Ranges" are
significantly different in size, that may be your problem.
Another cause is that Excel may think that your code modules are larger
than they are. But this wouldn't cause the size growth that you are seeing.
Post back and tell us what you find with the Used Range test above. HTH
Otto
"Mark" <[email protected]> wrote in message
news:[email protected]...
>I am currently building a workbook with multiple pages which tie data
> between various tabs within the same workbook. There are a number of
> interations that the data is going through; however, there are no pivot
> tables, no references to other workbooks, and 2 very small macros.
> However,
> the file size recently jumped from 5meg to 10meg.. and then again from
> 10meg
> to 30meg.
>
> Any ideas on what could have caused the problem?
>
> Currently, I am reworking some of my equations early on in the data
> processing, so there are a number of #ref errors on later pages. Could
> this
> be why?

[JimmyDew]

Mark, I'm having the same problem, just not as bad as yours...400K to 2Mb or
so, and getting bigger all the time.

Everyone suggest the CTRL-END method, and one of my sheets did have a large
range specified...I deleted it, re-saved the file, and the problem didn't go
away.

Did you find another solution?

"Mark" wrote:

> I am currently building a workbook with multiple pages which tie data
> between various tabs within the same workbook. There are a number of
> interations that the data is going through; however, there are no pivot
> tables, no references to other workbooks, and 2 very small macros. However,
> the file size recently jumped from 5meg to 10meg.. and then again from 10meg
> to 30meg.
>
> Any ideas on what could have caused the problem?
>
> Currently, I am reworking some of my equations early on in the data
> processing, so there are a number of #ref errors on later pages. Could this
> be why?

[Gord Dibben]

Jimmy

Most effective method of re-setting the used range is to select all the rows
below your real range.

Select row header below last data row.

SHIFT + END + DownArrow

Edit>Delete>Entire Row. DO NOT just Clear Contents.

Do same for columns to the right of your data.

This must be done for each worksheet in the workbook.

Save and Close then re-open.


Gord Dibben Excel MVP

On Wed, 13 Apr 2005 10:39:06 -0700, "JimmyDew"
<[email protected]> wrote:

>Mark, I'm having the same problem, just not as bad as yours...400K to 2Mb or
>so, and getting bigger all the time.
>
>Everyone suggest the CTRL-END method, and one of my sheets did have a large
>range specified...I deleted it, re-saved the file, and the problem didn't go
>away.
>
>Did you find another solution?
>
>"Mark" wrote:
>
>> I am currently building a workbook with multiple pages which tie data
>> between various tabs within the same workbook. There are a number of
>> interations that the data is going through; however, there are no pivot
>> tables, no references to other workbooks, and 2 very small macros. However,
>> the file size recently jumped from 5meg to 10meg.. and then again from 10meg
>> to 30meg.
>>
>> Any ideas on what could have caused the problem?
>>
>> Currently, I am reworking some of my equations early on in the data
>> processing, so there are a number of #ref errors on later pages. Could this
>> be why?

[JimmyDew]

Sorry, Gord, but I didn't make myself clear. I did mention the Ctrl-End
method, but what I meant by that was all my worksheets were OK using either
method, the Ctrl-End or selecting the rows and columns, and deleting them.
It wasn't, apparently, the "Used Range" that was the problem, and I've given
up on figuring out what exactly was causing my problem, and concentrated on
getting it to work.

The end to my story was pretty simple, actually. I isolated the two sheets
(there are 23 sheets in this workbook) causing this "bloat", deleted them,
and re-made them. Problem solved! I went from 450K on original file, to
1.2Mb over a period of time (file kept getting bigger), now back to 450K.

I am still curious what caused the problem (one of the sheets was just a
simple "copy from internet page and paste into worksheet" job, but after
spending a few hours on it I've decided the time spent just wasn't worth it.
Thanks for the thoughts, anyway.

"Gord Dibben" wrote:

> Jimmy
>
> Most effective method of re-setting the used range is to select all the rows
> below your real range.
>
> Select row header below last data row.
>
> SHIFT + END + DownArrow
>
> Edit>Delete>Entire Row. DO NOT just Clear Contents.
>
> Do same for columns to the right of your data.
>
> This must be done for each worksheet in the workbook.
>
> Save and Close then re-open.