excel 多列合并一列 方法

Sub MergeDataToColumn()
Dim lastRow As Long, lastColumn As Long, newRow As Long
Dim i As Long, j As Long
Dim newData() As Variant

' 获取当前工作表的最后一行和最后一列
lastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

' 将数据存储到一个二维数组中
ReDim newData(1 To lastRow * lastColumn, 1 To 1)
newRow = 1
For i = 1 To lastRow
For j = 1 To lastColumn
newData(newRow, 1) = Cells(i, j).Value
newRow = newRow + 1
Next j
Next i

' 将数据写入新工作表的一列
With Worksheets.Add
.Name = "Merged Data"
.Range("A1").Resize(UBound(newData, 1), 1).Value = newData
End With

' 提示操作完成
MsgBox "数据已合并到新工作表。", vbInformation
End Sub