Thomas -

Even with screen updating disabled, selecting cells and especially
activating worksheets can slow down the code.

Another big time waster is looping through a worksheet range and adding
cells to a VBA array one by one, then looping through the array to
repopulate the worksheet. Much better is to move the range into a VBA
variant in one step, and move the array back in another step:

dim vArray as Variant
vArray = Worksheets(1).Range("A1:D10").Value

now you can refer to vArray as if it were dimensioned (1 to 10, 1 to 4).
When you are done manipulating the array, or if by now it's in a
different array, you assign the range values to the array:

iRow = ubound(MyArray,1)+1-lbound(MyArray,1)
iCol = ubound(MyArray,2)+1-lbound(MyArray,2)
Worksheets(2).Range("A1").resize(iRow,jCol).value = MyArray

When I first learned this trick on an old 486 machine, it was amazing
how much more quickly it worked. On these newer GHz machines, it's less
of a difference, but still noticeable.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Thomas Kregeloh wrote:

Hi Bob,
is this also true when screen updating is switched off?
Thomas


"Bob Phillips" schrieb im Newsbeitrag
...

The problem with select is that it is slow, and if there is a lot of it
going on, it can make quite a performance difference. If on top of that

you

move between worksheets, that compounds the problem. For instance, this is
the sort of code that the macro recorder creates

Range("A1").Select
Selection.Value = "abc"

can be more efficiently written as

Range("A1").Value = "abc"

The amount of work removed here, and the CPU cycles, is significant, but

the

result is exactly the same.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)