Writing 2nd dimension to Range from a 2D variant without looping in VBA

Question

I have a variant variable, and pass the following range values to it like so.

Option Base 1
Dim varEmployees As Variant

varEmployees = Range("A1:B5").Value

This 2D variant now has Employee IDs in the 1st dimension and Employee names in the 2nd. So we get something like the following.

varEmployees(1,1) = 1234    varEmployees(1,2) = John Doe
varEmployees(2,1) = 5678    varEmployees(2,2) = Jane Smith
varEmployees(3,1) = 9012    varEmployees(3,2) = Mary Major
varEmployees(4,1) = 3456    varEmployees(4,2) = Judy Stiles
varEmployees(5,1) = 7890    varEmployees(5,2) = Richard Miles

I want to write the 2nd dimension only back to a range without using a loop but when I use the following code...

Range("D1:D5") = varEmployees

I only get the 1st dimension as shown under Actual Results but what I want is my Desired Results (only the 2nd dimension).

Actual Results       Desired Results
--------------       ---------------
    1234             John Doe
    5678             Jane Smith
    9012             Mary Major
    3456             Judy Stiles
    7890             Richard Miles

Is there a way to do this or is there a rule about variant arrays that I am not aware of.

http://stackoverflow.com/questions/18481330/2-dimensional-array-vba-from-cell-contents-in-excel/18481730#18481730 — , Sep 20 '13 at 08:43

Tim Williams · Accepted Answer · 2013-09-20T00:25:04.830

7

Variant arrays obtained using someRange.Value are always 1-based, so you don't really need the Option Base 1

Here's a method which does what you want:

Sub Test()

    Dim a As Variant, b As Variant
    a = Range("A1:B5").Value

    'get a specific column
    b = Application.Index(a, 0, 2) '0="all rows"
    With Range("D10")
        .Resize(UBound(b), 1).Value = b
    End With

    'get a specific row
    b = Application.Index(a, 2, 0) '0="all cols"
    With Range("D21")
        .Resize(1, UBound(b)).Value = b
    End With        
End Sub

edited Sep 20 '13 at 00:25

answered Sep 20 '13 at 00:16

Tim Williams

154,628
8
97
125

+1! I did not know that `Application.Index` could slice a row/column from an array. – David Zemens Sep 20 '13 at 01:38
Thank you. I also didn't know we could use `Application.Index` that way. Guess I have to declare another variant variable to make this work. Anyway thank you once again. I looked up `Application.Index` and got this interesting site [**VBA Trick**](http://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/) – Django Sep 20 '13 at 03:38
You can do it without the intermediate array - I only included it to make the code easier to follow. – Tim Williams Sep 20 '13 at 03:44

Writing 2nd dimension to Range from a 2D variant without looping in VBA

1 Answers1