Excel VBA代码将数据从行转换到列 [英] Excel VBA code to transpose data from rows to columns

问题描述

我有A列数据，50000行数据。我需要将每6行数据转置为6列。例如A1：A6的数据必须转置到B1：G1。来自A7：A14的数据必须转置到B2：G2。我感谢任何人可以为此提供VBA代码。

列A中的数据如下所示：

  Col A 
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
  / pre> 
 
 
 
 
转置数据必须如下所示：col B to col G：
 
 
 
 
 
 列BCDEFG 
 1 2 3 4 5 6 
 7 8 9 10 11 12 
  
 
 
解决方案
我身边的其他变体：
  Sub TransposeRows2（）
 Dim i& z& x& 
i = Cells（Rows.Count，A）。End（xlUp）.Row 
z = 1：x = 1 
虽然z <= i 
范围（B & x）.Resize（，6）= _ 
 WorksheetFunction.Transpose（Range（A& z）.Resize（6））
z = z + 6：x = x + 1 
 Wend 
 End Sub 
  
测试：
 
 
 
  
 
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

解决方案
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:



                        
这篇关于Excel VBA代码将数据从行转换到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！