[dba-Tech] Excel bloat question

I found this on the net:

Jim Hale

Honey Can I Shrink the Spreadsheet ? 

Rodney POWELL <mailto:rodney at beyondtechnology.com>  
Microsoft MVP - Excel

The basic Excel workbook file size is only about 14 kb. Efficiently
written VBA code will not cause an Excel project to become excessively
bloated, but several things are more likely to cause a project to grow
larger than necessary are:

Saving a workbook in certain backward-compatible file formats.

Storing a lot of forms, controls, custom toolbars, or graphic images in
the workbook.

Either formatting in empty cells or having acres of cells with formulas.


This last item is the most common cause of Excel file bloat, since a
Worksheet's UsedRange properties continually expands to encompass the
entire area that has ever been used.


How to Reset the UsedRange Property

We want to get Excel to "forget" all that unecessary file size.
The way to overcome this manually is to delete the rows (and columns)
that formerly had data or formatting and save the file.
Select all the rows past the last populated row. You must select the
grey row labels so the entire rows will be actually removed and not just
cleared.
The same applies for excess columns.
>From the Edit menu, select the Delete > EntireRow command. Once this is
done, you need to Save the workbook. This will reset the UsedRange
property.

Note: You can also go into the VBE (Visual Basic Editor) and execute the
command ActiveSheet.UsedRange in the Immediate window to reset the
UsedRange without having to save.

Preventative Measures

When formatting, it is best to not include a large number of extra
cells, unless you format an entire column or row. If you format 1,000
cells, Excel stores 1,000 format specifications. If you format an entire
row or column, Excel only has one format to store.

Similarly, try to not extend formulas for thousands of rows if it can be
avoided. And, if your spreadsheet looks like one large patchwork quilt,
consider if maybe it can be restructured and organized into a few
separate efficient sheets within the same workbook.


-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Wednesday, May 16, 2007 3:04 PM
To: 'Discussion of Hardware and Software issues'
Subject: Re: [dba-Tech] Excel bloat question


Wow -- I had no idea -- thanks Jon! I wanted to write about bloat on an
Office blog, but didn't know if it really mattered.

Susan H.

Yes, totally. We've got some spreadsheets at work that just keep track
of courier requests etc, and they have lines added, removed, and
coloured maybe 10 times a day. After a year or so, they get to a big
enough size that some of the lower end PCs just can't open them, and
that's only around the 5-6mb mark.


_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com




***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.