mydreamnet
New Member
- Joined
- Aug 13, 2017
- Messages
- 3
Sub MoveCols()
Dim a As Variant, b As Variant
Dim i As Long, j As Long, k As Long
With Range("E4").CurrentRegion
a = .Value
ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 1)
For j = 1 To UBound(a, 2)
For i = 1 To UBound(a, 1)
k = k + 1
b(k, 1) = a(i, j)
Next i
Next j
.ClearContents
.Resize(UBound(b), 1).Value = b
End With
End Sub
Welcome to the MrExcel board!
For data set out like in your first image, try this on a copy of the data.
Code:Sub MoveCols() Dim a As Variant, b As Variant Dim i As Long, j As Long, k As Long With Range("E4").CurrentRegion a = .Value ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 1) For j = 1 To UBound(a, 2) For i = 1 To UBound(a, 1) k = k + 1 b(k, 1) = a(i, j) Next i Next j .ClearContents .Resize(UBound(b), 1).Value = b End With End Sub
=IFERROR(INDEX($B$1:$H$3,MOD(SMALL(IF($B$1:$H$3<>"",(COLUMN($B$1:$H$3)-COLUMN($B$1)+1)*10^9+ROW($B$1:$H$3)-ROW($B$1)+1),ROWS($A$4:A4)),10^9),INT(SMALL(IF($B$1:$H$3<>"",(COLUMN($B$1:$H$3)-COLUMN($B$1)+1)*10^9+ROW($B$1:$H$3)-ROW($B$1)+1),ROWS($A$4:A4))/10^9))," ")
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody> </tbody> |