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
>
>