Slow - VBA Copy Paste recalculate

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
653
I was testing for bottlenecks in some project finance code to test for IRR and the bottle next appears to be on bit where the range is moved before a recalculation occurs to see how far away the answer is from being solved.

Code:
Call MoveDate(Names("CopyRange").RefersToRange, Names("PasteRange").RefersToRange)

Code:
Sub MoveDate(rngCopy As Range, rngPaste As Range)


rngPaste.Value2 = rngCopy.Value2   ' Takes around 1.2 - 1.5 seconds to run
Application.calculate                        ' Takes around 1.5 - 1.8 seconds to run


End Sub
The above is part of a wider macro that takes ~200 seconds to solve and this part accounts for ~75% of the time with it being called lots of times. each line taking over a second to complete.

Can anyone think of any better ways of completing this task in a more efficient way?
The data table is 3096 cells, 36 * 86
Calculate mode is set to manual, screen updates and enable events are set to False
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,099,000
Messages
5,465,938
Members
406,453
Latest member
MarkB5432

This Week's Hot Topics

Top