I have data in column A with 50000 rows of data. I need to transpose every 6 rows of data to 6 columns. For example data from A1:A6 must be transposed to B1:G1. Again data from A7:A14 must be transposed to B2:G2. I appreciate if anyone can provide VBA code for this.
Data I have in column A is as shown below:
Col A
1
2
3
4
5
6
7
8
9
10
11
12
The transpose data must be as shown below in col B to col G:
Columns B C D E F G
1 2 3 4 5 6
7 8 9 10 11 12
Try this:
Sub TransposeRows()
Dim rng As Range
Dim i As Long
Set rng = Range("A1")
While rng.Value <> ""
i = i + 1
rng.Resize(6).Copy
Range("B" & i).PasteSpecial Transpose:=True
Set rng = rng.Offset(6)
Wend
Application.CutCopyMode = False
End Sub
Got this from here.
additional variant from my side:
Sub TransposeRows2()
Dim i&, z&, x&
i = Cells(Rows.Count, "A").End(xlUp).Row
z = 1: x = 1
While z <= i
Range("B" & x).Resize(, 6) = _
WorksheetFunction.Transpose(Range("A" & z).Resize(6))
z = z + 6: x = x + 1
Wend
End Sub
tested:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With