Excel VBA双指定条件筛选赋值的最快优化方法

问题场景 #

操作对象为gar_nv工作表，共2553行数据、135个已命名列。需求为定位同时匹配produit、acte两个值的行，将变量Remb写入对应Col列的匹配行位置。
原有逐行操作单元格的实现逻辑如下：

For Line = 1 To gar_nv.Cells(Rows.Count, 1).End(xlUp).Row
    'produit, acte, Col and Remb are variables defind above
    If gar_nv.Range("GCGAR6").Rows(Line).Value Like produit Then
        If gar_nv.Range("GCBARB").Rows(Line).Value Like acte Then
            gar_nv.Range(Col).Rows(Line).Value = Remb    
        End If
    End If
Next Line

原有代码速度慢的核心原因 #

原有代码在循环中逐次读取、写入单元格，每一次单元格操作都会触发和Excel的COM交互，这类交互的开销远大于内存运算，哪怕数据量只有几千行，也会产生明显的等待时间。

最优优化方案 #

核心思路是把需要处理的单元格区域批量读入内存数组，在内存中完成全部判断和赋值操作，最后一次性把结果写回工作表，可以完全消除循环内的COM交互开销，执行速度比原有代码提升10~100倍，且完全兼容Like模糊匹配逻辑。
优化后可直接使用的代码：

Sub FastUpdate()
    Dim lastRow As Long
    Dim arr_produitCol As Variant, arr_acteCol As Variant, arr_targetCol As Variant
    Dim i As Long
    
    ' 临时关闭Excel非必要功能，减少额外开销
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim calcMode As XlCalculation
    calcMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    ' 循环外一次性获取最后一行行号，避免重复计算
    lastRow = gar_nv.Cells(gar_nv.Rows.Count, 1).End(xlUp).Row
    
    ' 批量将需要判断的两列、待写入的目标列读入内存数组
    arr_produitCol = gar_nv.Range("GCGAR6").Resize(lastRow, 1).Value
    arr_acteCol = gar_nv.Range("GCBARB").Resize(lastRow, 1).Value
    arr_targetCol = gar_nv.Range(Col).Resize(lastRow, 1).Value
    
    ' 纯内存遍历判断，无单元格交互
    For i = 1 To lastRow
        If arr_produitCol(i, 1) Like produit And arr_acteCol(i, 1) Like acte Then
            arr_targetCol(i, 1) = Remb
        End If
    Next i
    
    ' 一次性将结果写回工作表
    gar_nv.Range(Col).Resize(lastRow, 1).Value = arr_targetCol
    
    ' 恢复Excel原有设置
    Application.Calculation = calcMode
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

其他可选方案 #

如果你的匹配是精确匹配（不需要使用Like通配符），还可以使用自动筛选、Range.Find方法批量定位匹配行后直接写入，不需要遍历全量行，速度还能进一步提升，但如果需要保留Like模糊匹配能力，内存数组方案是通用性和速度平衡的最优选择。

优化注意点 #

• 所有固定参数、固定对象引用都要放在循环外提前获取，不要在循环中重复计算、重复调用Range对象。
• 临时关闭屏幕更新、事件触发、自动重算这三个设置，能避免处理过程中触发不必要的界面刷新、事件响应和公式重算，处理完成后记得恢复原有设置即可。

内容的提问来源于stack exchange，提问作者Jia Hannah