Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA code to transpose data from rows to columns

Tags:

excel

vba

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
like image 704
Siraj Avatar asked May 31 '16 12:05

Siraj


2 Answers

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.

like image 126
Mrig Avatar answered Sep 23 '22 01:09

Mrig


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:

enter image description here

like image 33
Vasily Ivoyzha Avatar answered Sep 26 '22 01:09

Vasily Ivoyzha