Wish to replace Application.Index call

[mookyon]

Dear Fellow Members,

I am looking for an faster alternative to this code:

Application.Index(rngParameters, 1) = Application.Index(varTests, t, 5)
Application.Index(rngParameters, 2) = Application.Index(varTests, t, 6)
Application.Index(rngParameters, 3) = Application.Index(varTests, t, 7)
Application.Index(rngParameters, 4) = Application.Index(varTests, t, 8)
Application.Index(rngParameters, 5) = Application.Index(varTests, t, 9)
Application.Index(rngParameters, 6) = Application.Index(varTests, t, 10)

Here I transpose six consecutive columns from a raw (number "t") in an internal array ("varTests") into a similar size vertical vector in the worksheet (named here "rngParameters").
Please note six elements are: (1) a consecutive part (colums in a row) of the array; (2) the entire vertical vector size in Excel (it includes six rows).

There should be a faster way…

Please advise.

THX

 

[offthelip]

what about something like this:

Dim temp(1 To 6, 1 To 1) As Variant
For i = 1 To 6
temp(i, 1) = Vartests(t, i + 4)
Next i
Range(rngParameters) = temp

 

[jasonb75]

what about something like this:

Perhaps without Range() in the last line as rngParameters is already a range

Although to be honest, the original code doesn't appear to be anything that should be noticeably slow with only 6 iterations. Perhaps the problem lies elsewhere in code that has not been shown here.

 

[Alex Blakenburg]

I am not sure if this is any faster but give it a go.
I have just added Transpose to a previous solution given by @Fluff here.

The output starts at B2:

Sub WriteAndTransposeSpecificColumns_Sent()

    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim rngSrc As Range, rngDest As Range
    Dim varTests As Variant
    
    Set shtSrc = Worksheets("Source")
    Set shtDest = Worksheets("Destination")
    
    Set rngSrc = shtSrc.Range("A1").CurrentRegion
    varTests = rngSrc
    
    ' Adaption of Fluff's code
    ' https://www.mrexcel.com/board/threads/vba-array-extract-3-columns.1140435/post-5523713
    Dim arrCols As Variant
    arrCols = Array(6, 7, 8, 9, 10)
    shtDest.Range("A1").Offset(1, 1).Resize(UBound(arrCols) + 1, UBound(varTests)) = Application.Transpose(Application.Index(varTests, Evaluate("Row(1:" & UBound(varTests) & ")"), arrCols))
  
End Sub

 

[mookyon]

Dear Conftributors,

Thank you all for your contributions. Finally, I replaced the former code with:

rngParameters = Application.Transpose(Application.Index(varTests, t, Array(5, 6, 7, 8, 9, 10)))

This code-section is inside a nested loop and hence the requirement for tight duration control.

I think relaying on either Application.WorksheetFunction.Index, or Application.Index might be costly.

Despite reaching sixfold reduction of the number of code lines, as well as halving the usage of Application.Index calls, I still hope somebody will come with a faster approach (without relying on worksheet-function...)

Rgds,

 

[RoryA]

I suggest you post more of your code so we can at least see some context.