Need Help to Speed up 2007/2010 Excel Macros

[Roncondor]

I have some Excel 2003 Macros that run 5 or 6 times slower in Excel 2007 / 2010. (Not sure how Microsoft accomplished such a wonderful feat!)

That speed is just not acceptable.

PLEASE Does anyone have any pointers for me on how to speed it up?

THANKS!

-----------------------------
Sub Test()

Dim I As Integer

With Application
.Iteration = False
.MaxChange = 0.001
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Calculate

For I = 1 To 500

Application.StatusBar = I

Range("c_assetloop").Value = I

Range("calc_mtginfo").Calculate

Range("newcell).value = Range("oldcell").value ' single cell

Range("Calc_Fillmtgdata").Calculate

Application.Goto reference:="Calc_MtgCfSheet"

ActiveSheet.Calculate

'=============================================
'THIS IS THE LINE THAT CAUSES THE SLOW DOWN
' CFS_TO and CFS_from are 10 x 120 arrays.
' CFS_TO is not referenced by any other cells
'=============================================
Range("cfs_to").Value = Range("cfs_from").Value
'=============================================

Next I

Application.StatusBar = False

Application.Calculation = xlAutomatic

Calculate

End Sub

 

[Jonmo1]

Putting the WHY aside....And concentrating on resolution...
Can't really say without understanding the purpose of the code...

Can you post:

The contents of the "Refers To" box for ALL the named rangeS?
c_assetloop
calc_mtginfo
newcell
oldcell
Calc_Fillmtgdata
Calc_MtgCfSheet
CFS_TO
CFRS_from

The names of all the related sheets (and which sheets are related to which named ranges).

And a sample set of data for each relavant sheet.



Or perhaps you would like to email your book to me?
Replacing confidential data, and providing a dummy dataset?

 

[sous2817]

I think it would be more helpful if you posted what's in your named ranges ( formulas, etc.)

For instance, if you have formulas that reference entire columns in 2003, depending on the formula, it will calculate a lot faster than the same formulas in 2007 and 2010 (because there are a lot less rows in 2003).

 

[Jonmo1]

I think you probably just hit the nail on the head...

I think it would be more helpful if you posted what's in your named ranges ( formulas, etc.)

For instance, if you have formulas that reference entire columns in 2003, depending on the formula, it will calculate a lot faster than the same formulas in 2007 and 2010 (because there are a lot less rows in 2003).

 

[voao]

Solutions:
Excel 2003: Options -> View -> "uncheck" show page break
Excel 2007: top left office button -> Excel Options -> Advance -> under section Display options for this worksheet -> "uncheck" show page breaks