How do you switch rows and columns in Excel VBA?

Excel VBA code to transpose data from rows to columnsExcel VbaSolution 1: [1]Try this:Sub TransposeRows() Dim rng As Range Dim i As Long Set rng = Range("A1") Whil

How do you switch rows and columns in Excel VBA?

Excel VBA code to transpose data from rows to columnsExcel Vba

Solution 1: [1]

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.

Solution 2: [2]

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:

enter image description here


Solution 3: [3]

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


Solution 4: [4]

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)

Credits

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack OverflowSolutionCreditSolution 1MrigSolution 2Gary's StudentSolution 3Vasily IvoyzhaSolution 4Miguel Ângelo Rodrigues

Video liên quan