How to transpose horizontal array into vertical array in VBA

Question

Why does the MsgBox not function when a horizontal array has been converted to a vertical array even though both array's return the same VarType?

Horizontal Array Transposed: [Does Not Work]

Dim Horizontal_Array As Variant
Horizontal_Array = Worksheets(sh.name).Range("A1:C1")
Horizontal_Array = Application.WorksheetFunction.Transpose(Horizontal_Array)
MsgBox Join(Horizontal_Array, vbCrLf)

Desired Output: Key1 Key2 Key3

Vertical Array [Works]

Dim Vertical_Array As Variant
Vertical_Array = Worksheets(sh.name).Range("A1:A4")
Vertical_Array = Application.WorksheetFunction.Transpose(Vertical_Array)
MsgBox Join(Vertical_Array, vbCrLf)

Output: Key1 One Four Seven

Key1	Key2	Key3
One	Two	Three
Four	Five	Six
Seven	Eight	Nine

Tim Williams · Accepted Answer · 2022-07-07 01:21:00Z

1

A horizontal array needs two Transpose operations:

Dim Horizontal_Array As Variant
Horizontal_Array = Worksheets(sh.name).Range("A1:C1")

Horizontal_Array = Application.WorksheetFunction.Transpose( _
         Application.WorksheetFunction.Transpose(Horizontal_Array))
MsgBox Join(Horizontal_Array, vbCrLf)

See discussion here: Why does Join() need a double transposition of a 1-dim Long array?

edited Jul 7, 2022 at 1:21

answered Jul 7, 2022 at 1:12

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

How to transpose horizontal array into vertical array in VBA

Horizontal Array Transposed: [Does Not Work]

Vertical Array [Works]

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Horizontal Array Transposed: [Does Not Work]

Vertical Array [Works]

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related