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.
Thanks for reading
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.
Thanks for reading
