Need help in converting from rows to columns

onir0110 

If you are open to a VBA solution, please place the following code on a Standard Module like Module1.

In the attached, click on the Transpose Data button to get the desired output.

Sub TransposeData()
Dim lr As Long
Dim rngArea As Range
Dim Rng     As Range
Dim destCol As Long
Dim r       As Long

Application.ScreenUpdating = False

lr = Cells(Rows.Count, "A").End(xlUp).Row
destCol = 5

On Error Resume Next
Set rngArea = Range("A2:A" & lr).SpecialCells(xlCellTypeConstants, 3)
On Error GoTo 0
Cells(1, destCol).CurrentRegion.Offset(1).Clear
r = 2
If Not rngArea Is Nothing Then
    For Each Rng In rngArea.Areas
        Rng.Copy
        Cells(r, destCol).PasteSpecial xlPasteAll, Transpose:=True
        r = r + 1
        Application.CutCopyMode = 0
    Next Rng
End If
Cells(2, destCol).Select
Application.ScreenUpdating = True
End Sub