|
Honey Can I Shrink the Spreadsheet ?
Rodney POWELL
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. Let's demonstrate the problem.
- Start with a clean (new) worksheet and put some data in range A1:C8.
- Use the {Ctrl} + {End} key combination and the cursor will move to the end of the data [C8] as would be expected.
- Then if you enter a formula, value, or formatting in range
E10 and delete it, {Ctrl} + {End} moves
the cursor below the end of the remaining data.
In this case it's only a couple of extra rows and columns, but
sometimes this can result in thousands of extra rows !!
Now, multiply that by dozens of excess columns, and sometimes
several worksheets, and you can see how this can easily add
megabytes of extra weight to your project.
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.
Know an even better way?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
