I am using the code pasted in below to transpose a range. An example range is shown in row 133 in the minisheet below. The code produces the result shown in row 7-15.
I am trying to "expand" the code so I can adjust the number of columns to be transposed, from minimum 2 cols to maximum 10. Any good ideas on how to do that best? ( I can't get it to work at all)
This is the code I am using:
I am trying to "expand" the code so I can adjust the number of columns to be transposed, from minimum 2 cols to maximum 10. Any good ideas on how to do that best? ( I can't get it to work at all)
This is the code I am using:
VBA Code:
Sub Transpose()
Dim Ary As Variant, Nary As Variant
Dim r As Long, nr As Long, c As Long
Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
ReDim Nary(1 To UBound(Ary) * 5, 1 To 4)
For r = 1 To UBound(Ary)
For c = 5 To UBound(Ary, 2)
nr = nr + 1
Nary(nr, 1) = Ary(r, 1)
Nary(nr, 2) = Ary(r, 2)
Nary(nr, 3) = Ary(r, c)
Next c
Next r
Sheets("Sheet1").Range("M1").Resize(nr, 3).Value = Nary
End Sub
Example.xlsx | |||
---|---|---|---|
G | |||
7 | |||
Sheet1 |