Adding a specific array row to a range

Question

I'm working with a 3x3 2D array.

I'd like to take the 2nd row of the array and dump those values in a range on the worksheet.

Then i'd like to access the third row of the array and do the same thing again, and place those onto another part of the worksheet.

Is there a built in function to access a specific array row and all their values so that you can dump the whole row onto the worksheet somewhere?

BigBen · Accepted Answer · 2020-01-27 00:35:15Z

1

You could just use Index here. As an example:

Sub Test()
    Dim x(1 To 3, 1 To 3)

    x(2, 1) = "a"
    x(2, 2) = "b"
    x(2, 3) = "c"

    Range("A1:C1").Value = Application.Index(x, 2)
End Sub

answered Jan 27, 2020 at 0:35

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

4 Comments

btew2102 Over a year ago

Thanks Big Ben! What if I wanted to dump say the second row of the array but instead of all three values in the row (2,1), (2,2) and (2,3) I just wanted the first 2 values (2,1) and (2,2)? Further, I wanted the second row minus the 1st element and just the final two values in the second row?

JvdV Over a year ago

@btew2102, for example: Range("A1:B1").Value = Application.Index(x, 2, Array(1, 2)) to get first and second element. Or Range("A1:B1").Value = Application.Index(x, 2, Array(2, 3)) to get second and third.

btew2102 Over a year ago

Thanks BigBen. Two other things have come to me now. What if I would like to take the 3rd row of an empty 3x3 Array and fill the entire row with the same variable? Lastly, if I have three 3x3 Arrays (the first 2 are filled with variant data types and the third is empty) how do I fill the first 2 rows of the 3rd array with the first row from each of the 1st and 2nd arrays?

BigBen Over a year ago

@btew2102 - just use a loop here (or two) here.

Collectives™ on Stack Overflow

Adding a specific array row to a range

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related