Workbook is now Extremely Slow
 

Hi,

I have a very complicated workbook full of code, queries, macros etc....now
it has become so slow I can barely use it....it produces some complicated
reports and contains lots of automation ...first, how can I find out what is
slowing it down, second, how can I spped...it up...

Thanx

David

Hi David

Think you may have answered your own question as to what is slowing it down.
If the workbook is large with lots of formulas etc then it will take more
processing by your pc.
Check the file size and make sure excel hasn't eaten up large chucks of
memory with blank cells This happens in 97 and 2000. Sometimes rows and
columns that appear empty are not. This is usually obvious by pressing
Ctrl+End to find out the last cell. See if this is way off from where you
expect. Then delete the entire rows or column and resave the workbook.
Other than that try and simplify the workbook by splitting it in to several
linked files or get a bigger faster PC.
Adding extra memory to your PC is one of teh easiest ways to improve
performance.

Nick


"Dmorri254" wrote in message
...
Hi,

I have a very complicated workbook full of code, queries, macros
etc....now
it has become so slow I can barely use it....it produces some complicated
reports and contains lots of automation ...first, how can I find out what
is
slowing it down, second, how can I spped...it up...

Thanx

David

If you have lots of volatile formulas such as SUMPRODUCT it will get very
slow due to the number of calculations performed every time you change
something.
One method I've used to overcome this is by using code to enter formulas in
each sheet using the WorkSheet_Activate event, and Copy Paste Special
Values
using WorkSheet_Deactivate. Also its sometimes possible to restrict the
formulas to only the parts of the worksheet that need to be calculated.
This bit of example code below cut the calculation time on a spreadsheet
from over two minutes to just a few seconds. The INDIRECT(BBB) etc ranges
select the part of the sheet that needs to be calculated and searches that,
(about 50 rows), instead of the whole range which is over 3000 rows.


ActiveSheet.Range("D2:X41").Formula = _
"=SUMPRODUCT((INDIRECT(BBB)=Works!$C2)*(INDIRECT( BBB)<=Works!$D2)*(Input!$E$1:$Z$1=D$1)*(INDIRECT(A AA)=$A2)*(INDIRECT(CCC)))"
ActiveSheet.Range("D2:X41").Copy
ActiveSheet.Range("D2:X41").PasteSpecial xlPasteValues
ActiveSheet.Range("B2:B41").Formula = _
"=SUMPRODUCT((INDIRECT(BBB)=Works!$C2)*(INDIRECT( BBB)<=Works!$D2)*(INDIRECT(AAA)=A2))"
ActiveSheet.Range("B2:B41").Copy
ActiveSheet.Range("B2:B41").PasteSpecial xlPasteValues

Regards,
"Nick" wrote in message
...
Hi David

Think you may have answered your own question as to what is slowing it
down.
If the workbook is large with lots of formulas etc then it will take more
processing by your pc.
Check the file size and make sure excel hasn't eaten up large chucks of
memory with blank cells This happens in 97 and 2000. Sometimes rows and
columns that appear empty are not. This is usually obvious by pressing
Ctrl+End to find out the last cell. See if this is way off from where you
expect. Then delete the entire rows or column and resave the workbook.
Other than that try and simplify the workbook by splitting it in to
several linked files or get a bigger faster PC.
Adding extra memory to your PC is one of teh easiest ways to improve
performance.

Nick


"Dmorri254" wrote in message
...
Hi,

I have a very complicated workbook full of code, queries, macros
etc....now
it has become so slow I can barely use it....it produces some complicated
reports and contains lots of automation ...first, how can I find out what
is
slowing it down, second, how can I spped...it up...

Thanx

David

You could also change the preference to manual calculation. that way you
could enter data without having calculations happening after every entry.

"Dmorri254" wrote:

Hi,

I have a very complicated workbook full of code, queries, macros etc....now
it has become so slow I can barely use it....it produces some complicated
reports and contains lots of automation ...first, how can I find out what is
slowing it down, second, how can I spped...it up...

Thanx

David