如何用VBA实现数据转置并完成隔列堆叠操作？

本人首次编写VBA代码，卡在如何一步完成数据转置与隔列堆叠的操作上。现有数据集为14列，行数不确定，需按照示例图中1至14的顺序整理数据。已尝试运行Stack Overflow上提供的相关代码，但无法根据自身需求重新配置，也不清楚如何将索引数据粘贴为新列。以下是一段可排序整数的VBA代码：

Sub Depth_Width_Sort()

Dim rngSource As Range
Dim rng As Range
Dim MiMatriz() As Long
Dim MiPos As Long
Dim i As Integer

Set rngSource = Range("A1").CurrentRegion

ReDim MiMatriz(1 To 1, 1 To rngSource.Cells.Count)

For Each rng In rngSource.Cells
    MiPos = (((rng.Row - rngSource.Cells(1, 1).Row + 1) - 1) * rngSource.Columns.Count) + (rng.Column - rngSource.Cells(1, 1).Column + 1)
    MiMatriz(1, MiPos) = rng.Value
Next rng

For i = 1 To rngSource.Cells.Count Step 2
    Cells((i + 1) / 2, 16).Value = MiMatriz(1, i)
Next i

For i = 2 To rngSource.Cells.Count Step 2
    Cells(i / 2, 17).Value = MiMatriz(1, i)
Next i

Erase MiMatriz

Set rngSource = Nothing
End Sub

优化后的VBA代码 #

针对14列数据集的转置隔列堆叠需求，同时添加索引列，修改后的代码如下：

Sub TransposeAndStack()
    Dim rngSource As Range
    Dim lastRow As Long, colCount As Integer
    Dim outputRow As Long, i As Integer, j As Integer
    
    ' 定义数据源区域（假设表头在A1，数据从A1开始）
    Set rngSource = Range("A1").CurrentRegion
    lastRow = rngSource.Rows.Count
    colCount = 14 ' 固定14列
    
    ' 初始化输出起始行（这里从第1列开始输出，可根据需求修改）
    outputRow = 1
    
    ' 遍历每一行数据
    For i = 1 To lastRow
        ' 遍历14列，每两列一组堆叠，同时添加行索引
        For j = 1 To colCount Step 2
            ' 写入索引列（第1列）
            Cells(outputRow, 1).Value = i
            ' 写入第j列数据
            Cells(outputRow, 2).Value = rngSource.Cells(i, j).Value
            ' 写入第j+1列数据（如果j+1不超过14）
            If j + 1 <= colCount Then
                Cells(outputRow, 3).Value = rngSource.Cells(i, j + 1).Value
            End If
            outputRow = outputRow + 1
        Next j
    Next i
    
    ' 释放对象
    Set rngSource = Nothing
End Sub

代码说明 #

• 索引列添加：通过i变量记录当前处理的源数据行号，直接写入输出区域的第1列
• 隔列堆叠逻辑：按每两列一组遍历14列，将每组数据逐行写入输出区域
• 自适应行数：通过lastRow = rngSource.Rows.Count自动获取源数据的总行数，无需手动指定
• 输出位置：当前代码从工作表第1行开始输出，若需要指定其他位置，修改outputRow的初始值即可（比如outputRow = lastRow + 2）

内容的提问来源于stack exchange，提问作者Andrew Shiang