Workbook is now Extremely Slow

[Dmorri254]

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

[Nick]

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" <Dmorri254@discussions.microsoft.com> wrote in message
news:AD546914-8091-41C0-AD57-045412775F9B@microsoft.com...
> 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

[Alan]

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(AAA)=$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" <nick@NoSpam.co.uk> wrote in message
news:%23NcXtB%23TFHA.3280@TK2MSFTNGP09.phx.gbl...
> 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" <Dmorri254@discussions.microsoft.com> wrote in message
> news:AD546914-8091-41C0-AD57-045412775F9B@microsoft.com...
>> 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
>
>

[rfhorn]

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

[vamosj]

At the beginning of each macro put the following

Application.ScreenUpdating = False

At the End put

Application.ScreenUpdating = True



This will allow the computer to work on the calculations without having to update the screen with each calculation. This is very handy when switching from sheet to sheet and then some.