Excel数据批量转置（多列转三列）

1、首先打开上例文件，如下图。

3、然后插入了一个模块1，在代码框中复制如下代码：Option Base 1Sub 表转换()'2019-11-30 20:11:38Dim r As Lon爿讥旌护g, m1, m2(), i As Long, j As Long, k As Longr = Range("a65536").End(xlUp).Row 'excel2003行65536 excel2007行1048576m1 = Range("a1:i" & r)ReDim m2((r - 1) * 8, 3)k = 0For i = 2 To r For j = 2 To 9 If m1(i, j) <> "" Then k = k + 1 m2(k, 1) = m1(i, 1) m2(k, 2) = m1(1, j) m2(k, 3) = m1(i, j) End If Next jNext iSheets("Sheet2").Range("a2:c" & k + 1) = m2End Sub

5、操作过程和运行结果动态操作如下图：