2

I want to copy and transpose a range multiple times and looking for the fastest way to do this. Have written to different ways which both are quite slow. They look like this:

...
For i = 1 To nrofSims
    simNr = i
    Application.Calculate
    Range(Sim.Range("Res3Top").Offset(1, 0), Sim.Range("Res3Top").Offset(simMonths, 0)).Copy
    Res3.Range("A4").Offset(i, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Next i
...

and

...
For i = 1 To nrofSims
    simNr = i
    Application.Calculate
    For j = 1 To simMonths
        Res3.Range("A4").Offset(i, j) = Sim.Range("Res3Top").Offset(j, 0).Value
    Next j
Next i
...

Is it possible to do something like rangePaste.value = rangeCopy.value and transposing the vector?

Edit:

Changed to the following code instead which is a little faster. But, as written in the comments, I maybe need the change the way my model is working right now.

...
Dim res3Sim As Range, res3Store As Range
Set res3Sim = Range(Sim.Range("Res3Top").Offset(1, 0), Sim.Range("Res3Top").Offset(simMonths, 0))
Set res3Store = Range(Res3.Range("A4"), Res3.Range("A4").Offset(0, simMonths - 1))

Dim i As Integer
For i = 1 To nrofSims
    simNr = i
    Application.Calculate
    res3Store.Offset(i, 1).Value2 = Application.Transpose(res3Sim.Value2)
Next i
...
David
  • 167
  • 1
  • 3
  • 10
  • 1
    Do you really need to recalculate in each iteration of loop? At first sight it looks like most time consuming part. Isn't it sufficient to recalculate after operations are done? – smartobelix Jun 07 '17 at 08:10
  • The range I'm copying is the result of changes in the workbook. So after every iteration in my simulation I want to save the results. The stupid part is that I need to transpose the range, due to make it analog to other result files and the "simulation functions" is easier to work with the way they are rather than transposing the calculations. – David Jun 07 '17 at 08:18
  • 1
    But could you just comment operation `Application.Calculate` just to see if it is not main reason of slow down? It make no sense to optimize something what is not most time-consuming part. Could be also helpful if you can post some sample of data or at least screenshot presenting what are trying to achieve. BTW in second solution you can for sure move `Application.Calculate` to outer loop. – smartobelix Jun 07 '17 at 09:33
  • @smartobelix Of course you're right, it makes little sense optimize something that's not the most time-consuming part. I'll look into re-building my model to make it less dependant on `Application.Calculate`. Also moved out the calculation-operation to the outer loop in the exemples. – David Jun 07 '17 at 09:56

0 Answers0