请求开发基于关键词的Excel行转列VBA宏（处理65000行数据）

问题描述 #

Excel的A列存有65000行数据，需实现以下功能：

• 每当某行包含无空格文本<Amt>时，将该行及之前的连续数据行（从上次关键词行的下一行开始）转置为一行的多列
• 示例：A1:A9（A9含<Amt>）转置至B1:J1；A10:A19（A19含<Amt>）转置至B2:K2
• 每行数据带有XML标识符（如A1以<AccountName>首尾包裹）

原代码存在的问题 #

提供的TransposeData代码存在以下关键问题：

• 关键词匹配错误：代码中判断的是<net>，而非需求中的<Amt>
• 分组逻辑错误：使用固定步长Step X遍历，无法适配每组行数不固定的场景（如示例中第一组9行、第二组10行）
• 转置效率低下：通过嵌套循环逐个赋值，未利用VBA内置的高效转置方法
• 目标行位置计算错误：部分场景下会导致数据写入位置偏移

修正后的VBA代码 #

Sub TransposeByAmtKeyword()
    Dim ws As Worksheet
    Dim lastRow As Long, currentRow As Long, targetRow As Long
    Dim startRow As Long
    Dim keyword As String
    
    ' 设置目标工作表，可根据实际修改为指定表名
    Set ws = ActiveSheet
    ' 定义匹配关键词
    keyword = "<Amt>"
    ' 初始化分组起始行与结果写入行
    startRow = 1
    targetRow = 1
    
    ' 获取A列最后一行数据行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历A列查找关键词行
    For currentRow = 1 To lastRow
        ' 检查当前行是否包含目标关键词
        If InStr(1, ws.Cells(currentRow, "A").Value, keyword, vbTextCompare) > 0 Then
            ' 将当前分组数据转置写入目标行的B列开始区域
            ws.Range(ws.Cells(targetRow, "B"), ws.Cells(targetRow, "B").Offset(0, currentRow - startRow)).Value = _
                Application.Transpose(ws.Range(ws.Cells(startRow, "A"), ws.Cells(currentRow, "A")).Value)
            
            ' 更新下一组的起始行与结果写入行
            startRow = currentRow + 1
            targetRow = targetRow + 1
        End If
    Next currentRow
    
    ' 处理最后一组无关键词的剩余数据（若存在）
    If startRow <= lastRow Then
        ws.Range(ws.Cells(targetRow, "B"), ws.Cells(targetRow, "B").Offset(0, lastRow - startRow)).Value = _
            Application.Transpose(ws.Range(ws.Cells(startRow, "A"), ws.Cells(lastRow, "A")).Value)
    End If
    
    ' 删除原A列的原始数据
    ws.Range("A1:A" & lastRow).Delete Shift:=xlToLeft
End Sub

代码说明 #

• 工作表适配：默认使用当前激活工作表，可修改Set ws = ActiveSheet为Set ws = ThisWorkbook.Sheets("你的表名")指定目标表
• 关键词匹配：使用vbTextCompare忽略大小写，若需严格区分大小写可移除该参数
• 高效转置：利用Application.Transpose直接将多行数据转置为多列，大幅提升65000行数据的处理效率
• 边界处理：包含对最后一组无关键词数据的处理，避免遗漏未分组的数据
• 数据清理：处理完成后自动删除原A列数据，仅保留转置后的结构化结果

内容的提问来源于stack exchange，提问作者Thomas Johnson