Fast way to output array into range

Question

How to output an array variable into range starting say from sheets("test").range("a1")? Would there be any way to output array into NamedRange without previous dump into cells?

Is there a way without looping?

Update. The number of elements in array is not constant and changes upon refresh or user input. The focus of my question is how to resize the range so it fits the array.

There's a couple of ways. It depends on the nature of your array. — , Mar 04 '19 at 13:26

Gary's Student · Accepted Answer · 2019-03-04T14:40:37.123

2

Simple examples for a 1-D array

array to row:

Sub arrayTest()
    Dim arr(1 To 3) As String
    Dim r1 As Range
    Set r1 = Range("A1:C1")

    arr(1) = "larry"
    arr(2) = "moe"
    arr(3) = "curly"

    r1 = arr
End Sub

or array to column:

Sub arrayTest2()
    Dim arr(1 To 3) As String
    Dim r1 As Range
    Set r1 = Range("A1:A3")

    arr(1) = "larry"
    arr(2) = "moe"
    arr(3) = "curly"

    r1 = Application.WorksheetFunction.Transpose(arr)
End Sub

A 2-D array is equally easy:

Sub arrayTest3()
    Dim arr(1 To 3, 1 To 2) As String
    Dim r1 As Range
    Set r1 = Range("A1:B3")

    arr(1, 1) = "larry"
    arr(2, 1) = "moe"
    arr(3, 1) = "curly"
    arr(1, 2) = "gary"
    arr(2, 2) = "student"
    arr(3, 2) = "whatever"

    r1 = arr

End Sub

EDIT#1:

Say we have an arbitrary 1-D array, either zero-based or one-based, and we want to push it into a row starting with cell B9:

Sub arrayTest4()
    Dim r1 As Range, U As Long, L As Long, rBase As Range
    Set rBase = Range("B9")
    arr = Array("qwert", 1, 2, 3, 4, "ytrew", "mjiop", "nhy789")

    L = LBound(arr)
    U = UBound(arr)
    Set r1 = rBase.Resize(1, U - L + 1)

    r1 = arr

End Sub

edited Mar 04 '19 at 14:40

answered Mar 04 '19 at 13:45

Gary's Student

95,722
10
59
99

My array is a result of a function and its size is not fixed. You assume it has fixed number of elements say 3 and that is why you use A1:A3 range. My array is dynamic based on user filter input. So I would have to somehow set up a range which fits the size of array. – Przemyslaw Remin Mar 04 '19 at 14:04
2

@PrzemyslawRemin IDK if the full range reference is needed, you might just need the top left cell. Otherwise use `Range("C3").Resize(rowCount,colCount)` - where `"C3"` is the top left cell - and set rowCount/colCount using `Ubound` on your array. – Greedo Mar 04 '19 at 14:13
@PrzemyslawRemin See my **EDIT#1** – Gary's Student Mar 04 '19 at 14:41
@Gary'sStudent That does it:-) I accept it. If you have any suggestion about feeding named range directly from array without dump to cells I would be grateful. – Przemyslaw Remin Mar 04 '19 at 14:50
@Greedo Thank you. Love that simplicity. – Przemyslaw Remin Mar 04 '19 at 14:51

score 0 · Answer 2 · answered Mar 06 '19 at 12:21

Here go two useful snippets I have build based on Gary's Student answer. Thank you Gary's Student!

Sub Array2DToRange()
    Dim Arr()
    ReDim Arr(0 To 2, 0 To 0) 'handles also Arr(1 to 3, 1 to 1)
    Arr(0, 0) = "Spinosaur"
    Arr(1, 0) = "T-Rex"
    Arr(2, 0) = "Triceratops"

    Dim R As Long, C As Long
    R = UBound(Arr, 1) - LBound(Arr, 1) + 1
    C = UBound(Arr, 2) - LBound(Arr, 2) + 1

    Range("k1").Resize(R, C).Value = Arr
End Sub

Sub Array1DToRange()
    Dim Arr()
    ReDim Arr(0 To 2) 'handles also Arr(1 to 3)
    Arr(0) = "Spinosaur"
    Arr(1) = "T-Rex"
    Arr(2) = "Triceratops"

    [a1].Resize(UBound(Arr) - LBound(Arr) + 1, 1) = Application.Transpose(Arr)
End Sub

Fast way to output array into range

2 Answers2

Linked