在excel VBA中重新组织列数据

Sub ReorganizeColumnData()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim sourceData As Variant
    Dim targetData() As Variant
    Dim numRows As Long
    Dim numCols As Long
    Dim i As Long
    Dim j As Long
    
    ' 设置源数据范围
    Set sourceRange = Range("A1:A10")
    numRows = sourceRange.Rows.Count
    numCols = sourceRange.Columns.Count
    
    ' 将源数据读入到数组中
    sourceData = sourceRange.Value
    
    ' 重新组织数据到新的数组中
    ReDim targetData(1 To numRows * numCols, 1 To 1)
    For i = 1 To numRows
        For j = 1 To numCols
            targetData((i - 1) * numCols + j, 1) = sourceData(i, j)
        Next j
    Next i
    
    ' 设置目标数据范围
    Set targetRange = Range("B1:B" & numRows * numCols)
    
    ' 将新的数据数组写入到目标范围中
    targetRange.Value = targetData
    
    ' 清除源数据
    sourceRange.ClearContents
End Sub