Horizontal to Vertical Lines of Text

[gtd526]

I want to convert the horizontal line of text (L2:S3) to a vertical line of text (L) in pairs of 2, as shown below.

 

[DanteAmor]

How about:

Sub Horizontal_Vertical()
  Dim c As Range, i As Long
  i = 5
  For Each c In Range("L2:S2")
    Range("L" & i).Resize(2).Value = Application.Transpose(Array(c, c.Offset(1)))
    i = i + 2
  Next
End Sub

 

[gtd526]

How about:

Sub Horizontal_Vertical()
  Dim c As Range, i As Long
  i = 5
  For Each c In Range("L2:S2")
    Range("L" & i).Resize(2).Value = Application.Transpose(Array(c, c.Offset(1)))
    i = i + 2
  Next
End Sub

I had to adjust some ranges with your code, but works perfectly!!

thx for your reply.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[alansidman]

Here is alternative means. Bring your data into Power Query. Unpivot your rows. Here is the Mcode
No VBA required or needed to learn to achieve this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"})
in
    #"Removed Columns"

If you wish to learn more about PQ, then click on the link in my signature.

 

[Rick Rothstein]

And yet one more way... a loopless macro:

Sub TransposeDoubleCellRange()
  Dim LastCol As String
  LastCol = Split(Cells(2, Columns.Count).End(xlToLeft).Address(, 0), "$")(0)
  Range("L5").Resize(2 * (Cells(1, LastCol).Column - 11)) = Application.Transpose(Split(Join(Evaluate(Replace("L2:@2&"" ""&L3:@3", "@", LastCol)))))
End Sub

Note: This macro assumes your data starts in cell L2. The 11 being subtracted is the number of the column immediately before the starting cell's column.