Excel VBA优化：批量复制40GP行并替换为40HC（解决数组空行问题）

原代码问题分析 #

原代码生成大量空行的核心原因是新数组索引计算逻辑错误：使用i + newRowCount作为新数组的行号，会导致原行与复制行之间出现空白位置。例如首次遇到"40GP"时，newRowCount变为1，i+newRowCount会跳过新数组的第i+1行，直接写入i+2行，从而留下空行。

优化后的VBA代码 #

Sub UpdateDataEfficiently()
    Dim ws As Worksheet
    Dim data As Variant
    Dim newData As Variant
    Dim lastRow As Long
    Dim i As Long, currentRow As Long, newRowCount As Long
    Dim j As Long ' 补充列循环变量声明
    
    ' 指定目标工作表
    Set ws = ThisWorkbook.Worksheets("sheet_1")
    
    ' 关闭Excel交互特性，大幅提升运行速度
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    ' 获取数据区域最后一行（基于A列判断）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' 将A10:H[lastRow]的数据读入数组
    data = ws.Range("A10:H" & lastRow).Value
    
    ' 统计需要新增的行数（C列为"40GP"的行数）
    newRowCount = 0
    For i = LBound(data, 1) To UBound(data, 1)
        If data(i, 3) = "40GP" Then
            newRowCount = newRowCount + 1
        End If
    Next i
    
    ' 定义新数组大小：原行数 + 新增行数
    ReDim newData(1 To UBound(data, 1) + newRowCount, 1 To UBound(data, 2))
    
    ' 填充新数组，确保无空行
    currentRow = 1
    For i = LBound(data, 1) To UBound(data, 1)
        ' 复制原行到新数组当前位置
        For j = 1 To UBound(data, 2)
            newData(currentRow, j) = data(i, j)
        Next j
        currentRow = currentRow + 1
        
        ' 若为"40GP"行，添加修改后的副本
        If data(i, 3) = "40GP" Then
            ' 复制原行数据
            For j = 1 To UBound(data, 2)
                newData(currentRow, j) = data(i, j)
            Next j
            ' 修改C列为"40HC"
            newData(currentRow, 3) = "40HC"
            currentRow = currentRow + 1
        End If
    Next i
    
    ' 将新数组写回原数据区域
    ws.Range("A10").Resize(UBound(newData, 1), UBound(newData, 2)).Value = newData
    
    ' 恢复Excel默认设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set ws = Nothing
End Sub

关键优化说明 #

• 索引逻辑修正：使用独立的currentRow变量跟踪新数组的写入位置，确保每一行数据连续填充，彻底解决空行问题。
• 性能提升：关闭屏幕更新、事件触发和自动计算，避免Excel频繁交互，处理5万行数据的速度会比单元格循环快数十倍。
• 明确工作表指定：直接绑定sheet_1，避免依赖活动工作表导致的执行错误。

内容的提问来源于stack exchange，提问作者Makubexho PC