vba实现excel多行多列转多行一列

选中部分数据区域（多行多列），例如：

转换之后：

vba代码：

Sub manyToOne()
   Dim TheRng, TempArr
   Dim i As Integer, j As Integer, elemCount As Long
   On Error GoTo line1
   Sheets("Sheet2").Range("a:a").ClearContents
   If Selection.Cells.Count = 1 Then
     Sheets("Sheet2").Range("a1") = Selection
   Else
     TheRng = Selection
     elemCount = UBound(TheRng, 1) * UBound(TheRng, 2)
     ReDim TempArr(1 To elemCount, 1 To 1)
     For i = 1 To UBound(TheRng, 2)
       For j = 1 To UBound(TheRng, 1)
         TempArr((i - 1) * UBound(TheRng, 1) + j, 1) = TheRng(j, i)
       Next
     Next
     Sheets("Sheet2").Range("a1:a" & elemCount) = TempArr
   End If
line1:
End Sub