Outputting partial array to range without looping?

Question

What is the quickest and most efficient way to output just a part of an array to range?

I can read a worksheet range to a VBA array easily enough:

Dim rng as Range
Dim arr() as Variant
set rng as whatever
arr = rng

And I can write an array to a worksheet just as easily:

rng = arr

But if I want to re-repulate only selected columns of the array to the worksheet, say columns 24-26:

For i = 2 To 413497
    For j = 24 To 26
        Cells(i, j) = arr(i, j)
    Next j
Next i

Is there a quickest way to do it without the for-next loop?

Answer 1

You could use application.index:

Sub tst()

Dim rng As Range
Dim arr() As Variant, x
Set rng = Range("a1:ab500000")
arr = rng

x = Application.Index(arr, [row(2:413497)], Array(24, 25, 26))
Sheets(2).Cells(2, 1).Resize(413497, 3).Value = x

End Sub

Answer 2

Sub TruncateArray()

    Dim oneDarray()
    Dim twoDarray()
    Dim shortArray() As String
    Dim longArray() As String

    longArray = Split("1,2,3,4,5,6,7,8,9,0", ",")

    shortArray = longArray

    ReDim Preserve shortArray(5)

    ActiveSheet.Range("A1:F1") = shortArray

    twoDarray = ActiveSheet.Range("A1:F1").Value

    oneDarray = Application.Transpose(Application.Transpose(twoDarray))

    ReDim Preserve oneDarray(1 To 3)

    ActiveSheet.Range("A2:C2") = oneDarray

End Sub