Empty output when populating 2D range with array

Question

I am trying to populate a 2D range with a 2D array.

At the moment I have this:

Sub populate()

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim Rows(2) As Variant

Rows(0) = [{1, 2, 3}]
Rows(1) = [{4, 5, 6}]
Rows(2) = [{7, 8, 9}]

With ws
    .Range(.Cells(1, 1), .Cells(3, 3)).Value = Rows
End With


End Sub

I do not get any errors ect., but the range remains empty after running the macro.

The weirdest thing is that I get the correct output when I go like:

With ws
    .Range(.Cells(1, 1), .Cells(3, 3)).Value = Application.Transpose(Application.transpose(Rows))
End With

I went through numerous articles and Microsoft Documents, but nothing came up to fix my problem.

Rows() is a 1-D array, whose elements are 1-D arrays. You need to get a genuine 2-D array — John Coleman
– John Coleman, Commented Nov 25, 2019 at 11:45
You already have a working option so it's not really clear to me what you're looking for? You could simply create a 2D array to start with: Dim Rows() As Variant: Rows() = [{1, 2, 3; 4, 5, 6; 7, 8, 9}] — Rory
– Rory, Commented Nov 25, 2019 at 12:03
If you want to use Evaluate then maybe try: Rows(0,0) = [Row(1:3)], Rows(1,0) = [Row(4:6)] and Rows(2,0) = [Row(7:9)]. Is that what you are trying to do? Check the locale variables window to see what all different options do to your arrays. — JvdV
– JvdV, Commented Nov 25, 2019 at 12:44
With the last comment of mine you can add a value through Rows(0, 0)(1, 1) = "Your value", this will change the value of the first element of the first 2D array in the first element of your 2D array =).......ehh, yes correct haha — JvdV
– JvdV, Commented Nov 25, 2019 at 13:18

JvdV · Accepted Answer · 2019-11-25 13:45:04Z

1

I'm not following why you are interested in a 2D-array within a 2D-array, but (also as per @Rory his comment, you can try the following:

Sub populate()

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Blad1")

Dim Rows(2, 0) As Variant

Rows(0, 0) = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]
Rows(1, 0) = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]
Rows(2, 0) = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]

'Rows(0,0)(1,1) = "value" 'If you want to change. This example will change the first element of the 2D-array within the first element of your first 2D-array.

With ws
    .Range(.Cells(1, 1), .Cells(3, 3)).Value = Application.Transpose(Application.Transpose(Rows(0, 0))) 'Will transpose your first element in your initial 2D-array (not sure if this is put correctly in English)
End With

End Sub

You can implement a For x = Lbound(rows) to Ubound(rows) if you want to do the above operation for each element in your 2D-array.

In chat it becomes clear OP also wants to implement the following:

reasonArr(1, 0) = [{0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0; 0,0,0,0,0,0,0,0,0,0,0,0}]

Which threw an error. This is because the provided line to Evaluate exceeds 255 characters.

edited Nov 25, 2019 at 13:45

answered Nov 25, 2019 at 13:31

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I. Я. Newb Over a year ago

Thank you, but I've got this already. Let's continue in chat.

Collectives™ on Stack Overflow

Empty output when populating 2D range with array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related