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?

score 3 · Accepted Answer · answered Sep 09 '19 at 10:33

3

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

answered Sep 09 '19 at 10:33

EvR

3,303
2
10
22

2

Indeed of perfect shortness +1); Side note: it's 413496, better using `.Resize(UBound(x),UBound(x,2))` - FYI [Have a look on this condensed overview to the advanced possibilites of the `Application.Index` function regarding array restructuring, inserting and even resorting](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call). So you might even append the first one based column counter `1` as last column and reverse order using `Array(26, 25, 24, 1)` :-) – T.M. Sep 09 '19 at 12:45
2

Lovely, very elegant. Thank you! – Michal Rosa Sep 09 '19 at 23:31

ProfoundlyOblivious · Answer 2 · 2019-09-09T07:12:30.927

0

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

edited Sep 09 '19 at 07:12

answered Sep 09 '19 at 06:48

ProfoundlyOblivious

1,400
1
5
10

Thanks but I don't see how it answers my question which is about 2D arrays. – Michal Rosa Sep 09 '19 at 06:51
@MichalRosa Sorry, missed that part because it is not prominently mentioned. Answer is updated to show how to handle 2D arrays as well. – ProfoundlyOblivious Sep 09 '19 at 07:05

Outputting partial array to range without looping?

2 Answers2