The Incredible Growing File

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I've got a problem. I've got an Excel workbook that I keep monthly with worksheets for each day. My problem is that I'm only half way through the month and it's already 40MB and loading slowly off the server. I'm curious what is causing this. There's not much on each sheet: a data table of about 190 rows and 15 columns. Some of these columns reference another workbook with Vlookup funcitons (I suspect these functions are at the heart of my problem). There are daily summary sections at the top of each sheet. There are also weekly summary sheets. So in total probably up to 30 worksheets in the whole workbook (This is another thing I suspect but it doesn't seem like worksheets themselves would take up that much room). The only other thing in the file is VBA to move all this stuff around effortlessly. Each Sheet has four command buttons to call the VBA. If anyone has a possible cause and solution to this problem you input is greatly appreciated.
 
Bob,

In all probability, your VBA code is formatting (or using) a lot
more space than you think it is.
Quick way to check is to press Ctrl + End on each of the worksheets
to see where Excel thinks the last cell is.
If it's not where it should be, take a look here for some help
on correcting it:

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

Now, if that is the problem, you'll need to correct your code
so that it doesn't happen again.

or.....

Not the right way, but tack on Debra's code from the site to
do it programmatically when your macro is done.

John

Bob Ewers said:
Hi all,

I've got a problem. I've got an Excel workbook that I keep monthly with
Click to expand...
worksheets for each day. My problem is that I'm only half way through the
month and it's already 40MB and loading slowly off the server. I'm curious
what is causing this. There's not much on each sheet: a data table of about
190 rows and 15 columns. Some of these columns reference another workbook
with Vlookup funcitons (I suspect these functions are at the heart of my
problem). There are daily summary sections at the top of each sheet. There
are also weekly summary sheets. So in total probably up to 30 worksheets in
the whole workbook (This is another thing I suspect but it doesn't seem like
worksheets themselves would take up that much room). The only other thing
in the file is VBA to move all this stuff around effortlessly. Each Sheet
has four command buttons to call the VBA. If anyone has a possible cause
and solution to this problem you input is greatly appreciated.
 
Back
Top