Excel 2007/2010 Slow; "Cell (Press ESC to cancel)"

[IM4FLSt]

Hi guys,

So I have a spreadsheet that contains a macro that copies a range of three cells (each of which contain a unique formula) and paste it into a large range. The following is the formula for each of the three cells:

=IF(ISBLANK(INDEX('Staged D'!$A$1:$EW$554,$A6,D$3)),"",INDEX('Staged D'!$A$1:$EW$554,$A6,D$3)+INDEX('Coord + C'!$G:$G,D$2,0))

=IF(ISBLANK(INDEX('Staged D'!$A$1:$EW$554,$A7,D$3)),"",INDEX('Staged D'!$A$1:$EW$554,$A7,D$3)+INDEX('Coord + C'!$J:$J,D$2,0))

=IF(ISBLANK(INDEX('Staged D'!$A$1:$EW$554,$A8,D$3)),"",INDEX('Staged D'!$A$1:$EW$554,$A8,D$3)+INDEX('Coord + C'!$M:$M,D$2,0))

I copy these three cells [Range(D6:D8)] to Range(D6:EX1652)

In Excel 2007 or 2010, this pastespecial formula operation takes about 60 seconds. In Excel 2003, it takes about 2.5 seconds. I timed it. Why does this take so much longer in the new Excel? This is slowing my macro down tremendously. I have tried turning off calculations, screen updating, and enable events but none of those made the operation any faster.

Any ideas on why the new and better Excel operates slower?

Thanks for the help.

John

 

[Joe Galvan]

When you are operating the Macro in 2007/2010, are you opening up a 2003 file? The slowing may be because it is running in compatability mode... Try re-creating the file in the 2007/2010 version.

I know the computers processor can also make a difference in calculation time... my work PC (SLow Dinosuar) and my home PC (Iseries processor), there is a huge difference!

 

[IM4FLSt]

See that's the odd thing. I created the spreadsheet in Excel 2010 using .xlsm and it runs that one operation very slowly. The computer is new and very fast with a quad core, and excel has multithreading enabled. (By the way, I also tried disabling multithreading and it didn't make a difference in the speed of this operation)

I then went to a computer that is about a decade old and ran it using Excel 2003, and it literally took only 2.8 seconds. It makes no sense to me. It has to be something that Excel 2007/2010 is doing that takes a long time to do (anywhere from 50 to 65 seconds).

 

[RoryA]

As a matter of interest, if you switch printers to the XPS document writer, is it any faster?

 

[IM4FLSt]

Also, it's not the macro that is slow, it is the one operation of copy paste that is slow; it runs just as slow when I copy and paste manually. So I don't believe it is related to bad macro code or anything, which is the reason I didn't post the code.

 

[IM4FLSt]

No sir. I have also tried it on another machine running Excel 2010 and a machine running Excel 2007, and both perform the copy paste operation slowly.

 

[IM4FLSt]

If anyone wants, I could upload a simple example spreadsheet that illustrates my point. If so let me know. (Also, tell me how to upload it. I'm new here and do not know how to attach files)

 

[RoryA]

You can't attach files here, I'm afraid.
I wonder if it might have to do with the fact that your formulas refer to entire columns - in 2007 and 2010 that's a lot more rows of data than in 2003.

 

[IM4FLSt]

rorya,

Thanks for your help and ideas. I hadn't thought of the entire column thing. I altered the formula to $G$6:$G$156, and so on in each of the three cells. But it still took right at 60 seconds, so it didn't speed up.

I kind of feel like I'm stuck with the new excel being slow.

John