Question for the real experts!

[diepenbos]

What does Excel do AFTER i calculated the sheet with (f9). (calculation is set to manual)

I have the following problem:
In cells F2-I5000 (and R2-U5000 AC2-AF5000 AO2-AR5000) are formulas reffering to cells in A2-E5000.(and m1-q5000 x1-ab5000 aj1-an5000)
In if I clear cells A2-E5000 (and the other ranges) with a macro then before F9 it is really fast.(1 second)
After F9 it's really slow(12 seconds)

The marco is this:

Cells(1, 1) = Now()
Range("A2:e5000").Select
Selection.ClearContents
Range("m1:q5000").Select
Selection.ClearContents
Range("x1:ab5000").Select
Selection.ClearContents
Range("Aj1:an5000").Select
Selection.ClearContents
Range("A1:a1").Select
Cells(1, 2) = Now()


So before F9 its fast and after i hit F9 its really slow.
I think ->Selection.ClearContents is what is slow.



ok thanks
frank
[email protected]

[Don Guillett]

try

Range("A2:e5000,m1:q5000,x1:ab5000").ClearContents

--
Don Guillett
SalesAid Software
[email protected]
"diepenbos" <[email protected]> wrote in message
news:[email protected]...
>
> What does Excel do AFTER i calculated the sheet with (f9). (calculation
> is set to manual)
>
> I have the following problem:
> In cells F2-I5000 (and R2-U5000 AC2-AF5000 AO2-AR5000) are formulas
> reffering to cells in A2-E5000.(and m1-q5000 x1-ab5000 aj1-an5000)
> In if I clear cells A2-E5000 (and the other ranges) with a macro then
> before F9 it is really fast.(1 second)
> After F9 it's really slow(12 seconds)
>
> The marco is this:
>
> Cells(1, 1) = Now()
> Range("A2:e5000").Select
> Selection.ClearContents
> Range("m1:q5000").Select
> Selection.ClearContents
> Range("x1:ab5000").Select
> Selection.ClearContents
> Range("Aj1:an5000").Select
> Selection.ClearContents
> Range("A1:a1").Select
> Cells(1, 2) = Now()
>
>
> So before F9 its fast and after i hit F9 its really slow.
> I think ->Selection.ClearContents is what is slow.
>
>
>
> ok thanks
> frank
> [email protected]
>
>
> --
> diepenbos
> ------------------------------------------------------------------------
> diepenbos's Profile:
http://www.excelforum.com/member.php...o&userid=21124
> View this thread: http://www.excelforum.com/showthread...hreadid=354763
>

[diepenbos]

Your tip doesnt help unfortunately.
The strange thing is before and after I hit the F9 key.
Before it takes 1 second
after it takes 12 seconds.
the calculation is manual,
the macro must only delete the cells

Does this have something to do with: Excel only tracks 65,536 dependencies to unique references?

I looks like it does something in the background.

[Don Guillett]

try this as first line

ActiveSheet.DisplaypageBreaks = false
and/or

application.ScreenUpdating = False

code
reset to true
--
Don Guillett
SalesAid Software
[email protected]
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> try
>
> Range("A2:e5000,m1:q5000,x1:ab5000").ClearContents
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "diepenbos" <[email protected]> wrote in message
> news:[email protected]...
> >
> > What does Excel do AFTER i calculated the sheet with (f9). (calculation
> > is set to manual)
> >
> > I have the following problem:
> > In cells F2-I5000 (and R2-U5000 AC2-AF5000 AO2-AR5000) are formulas
> > reffering to cells in A2-E5000.(and m1-q5000 x1-ab5000 aj1-an5000)
> > In if I clear cells A2-E5000 (and the other ranges) with a macro then
> > before F9 it is really fast.(1 second)
> > After F9 it's really slow(12 seconds)
> >
> > The marco is this:
> >
> > Cells(1, 1) = Now()
> > Range("A2:e5000").Select
> > Selection.ClearContents
> > Range("m1:q5000").Select
> > Selection.ClearContents
> > Range("x1:ab5000").Select
> > Selection.ClearContents
> > Range("Aj1:an5000").Select
> > Selection.ClearContents
> > Range("A1:a1").Select
> > Cells(1, 2) = Now()
> >
> >
> > So before F9 its fast and after i hit F9 its really slow.
> > I think ->Selection.ClearContents is what is slow.
> >
> >
> >
> > ok thanks
> > frank
> > [email protected]
> >
> >
> > --
> > diepenbos
> > ------------------------------------------------------------------------
> > diepenbos's Profile:
> http://www.excelforum.com/member.php...o&userid=21124
> > View this thread:
http://www.excelforum.com/showthread...hreadid=354763
> >
>
>