Excel VBA code to transpose data from rows to columns

Question

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:

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

Mrig · Accepted Answer · 2016-05-31 12:17:35Z

9

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.

answered May 31, 2016 at 12:17

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vasily · Accepted Answer · 2016-05-31 12:55:29Z

7

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:

answered May 31, 2016 at 12:55

Vasily

5,7923 gold badges22 silver badges36 bronze badges

Comments

Gary's Student · Accepted Answer · 2016-05-31 13:24:33Z

7

You do not need a macro for this. In B1 enter:

=OFFSET($A$1,COLUMNS($A:A)-1+(ROWS($1:1)-1)*6,0)

Then copy both across and down:

answered May 31, 2016 at 13:24

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

Siraj Over a year ago

Thanks, but if dragged/copied it duplicates to all cells.

Miguel Ângelo Rodrigues · Accepted Answer · 2017-03-21 11:38:02Z

0

In Portuguese Excel, "Gary's Student"'s formula, for 4 columns instead of 6, becomes:

=DESLOCAMENTO($A$1;COLS($A:A)-1+(LINS($1:1)-1)*4;0)

edited Mar 21, 2017 at 11:38

answered Mar 20, 2017 at 11:42

Miguel Ângelo Rodrigues

891 silver badge7 bronze badges

Collectives™ on Stack Overflow

Excel VBA code to transpose data from rows to columns

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related