File size

[LiAD]

Hi,

I have an excel folder which has four copy paste delete type macros working
between about 10 sheets and several large databases of 2000 products and
machines with index, match and calculation functions.

The file is growing at a collosal rate, its now 84M!

Is there any way of reducing this without having to start from scratch?

Thanks
LiAD

 

[Gary''s Student]

1. If you have pivot tables, consider not saving them with the file. Clear
the table and re-create them with macros after you have opened the file.

2. If you have the same equation/formula pasted down 10's of thousands of
rows, consider not populating the column until after the file is opened and
then doing the copy with a macro.

 

[Roger Govier]

Hi

Check each sheet to find where Excel thinks the last cell is.
Type Control+End and see where it takes you.
If it is beyond where your data extends in either rows or columns, delete
all columns to the right of your data and delete all rows below your last
row of data or the last row where you NEED to have formulae. Then save the
file and re-open.
If you have copied formulae all the way down the sheet just to allow for
future data entry, do you really need that?
Only copy down for as far as you can really expect to enter data, or better
still write a macro to copy formulae down in blocks of say a further 100
rows below the last used row when you open the file.

Have you got Conditional Formatting set for the whole sheet? If so, change
it to only cover the range that you need formatted.
Where possible, use Pivot Tables for your calculations rather than lots of
Sumproduct or Array formulae.
Used Dynamic Ranges rather than giving huge ranges in your formulae. This
won't save space but will probably improve speed.
In terms of speed of opening, ensure the macros that run have

Application.ScreenUpdating = False
Application.CalculationMode= xlManual
at the start of the macro, and

Application.ScreenUpdating = True
Application.CalculationMode= xlAutomatic
set at the end

 

[LiAD]

Good tips.

Thanks

Hi

Check each sheet to find where Excel thinks the last cell is.
Type Control+End and see where it takes you.
If it is beyond where your data extends in either rows or columns, delete
all columns to the right of your data and delete all rows below your last
row of data or the last row where you NEED to have formulae. Then save the
file and re-open.
If you have copied formulae all the way down the sheet just to allow for
future data entry, do you really need that?
Only copy down for as far as you can really expect to enter data, or better
still write a macro to copy formulae down in blocks of say a further 100
rows below the last used row when you open the file.

Have you got Conditional Formatting set for the whole sheet? If so, change
it to only cover the range that you need formatted.
Where possible, use Pivot Tables for your calculations rather than lots of
Sumproduct or Array formulae.
Used Dynamic Ranges rather than giving huge ranges in your formulae. This
won't save space but will probably improve speed.
In terms of speed of opening, ensure the macros that run have

Application.ScreenUpdating = False
Application.CalculationMode= xlManual
at the start of the macro, and

Application.ScreenUpdating = True
Application.CalculationMode= xlAutomatic
set at the end

 

[LiAD]

Helps quite a bit actually. Cheers

Thanks

1. If you have pivot tables, consider not saving them with the file. Clear
the table and re-create them with macros after you have opened the file.

2. If you have the same equation/formula pasted down 10's of thousands of
rows, consider not populating the column until after the file is opened and
then doing the copy with a macro.