如何将Excel VBA中的Range3设置为可变范围并优化代码速度

一、动态获取实际数据范围 #

你当前代码中固定写死Range3 = "B2:B150000"会导致处理大量空单元格，既浪费资源又拖慢速度。可以通过获取B列最后一个有数据的行号，来动态定义范围：

Sub PROP()
    Dim wsRawData As Worksheet
    Dim Rng1 As Range
    Dim lastRow As Long
    
    ' 直接绑定工作表对象，比用字符串更高效且不易出错
    Set wsRawData = ThisWorkbook.Worksheets("Raw Data")
    
    ' 获取B列最后一个非空单元格的行号
    With wsRawData
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        ' 确保至少有B2这个单元格（避免数据只有B1时出错）
        If lastRow < 2 Then lastRow = 2
        Set Rng1 = .Range("B2:B" & lastRow)
    End With
    
    ' 后续你的代码逻辑...
    
End Sub

二、额外提升运行速度的常用技巧 #

除了缩小处理范围，以下操作能大幅提升VBA代码运行效率：

• 关闭屏幕更新：避免代码执行时的界面刷新
• 设置手动计算：防止每次单元格操作都触发自动计算
• 禁用事件触发：避免代码执行时触发工作表事件（如Worksheet_Change）
• 用数组替代单元格循环：将数据一次性读入内存数组处理，再批量写回工作表

整合这些技巧的完整示例：

Sub PROP_Optimized()
    Dim wsRawData As Worksheet
    Dim Rng1 As Range
    Dim lastRow As Long
    Dim originalCalc As XlCalculation
    
    ' 保存原有计算模式，后续恢复
    originalCalc = Application.Calculation
    
    ' 开启提速设置
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    On Error GoTo Cleanup ' 确保出错时也能恢复设置
    
    Set wsRawData = ThisWorkbook.Worksheets("Raw Data")
    
    With wsRawData
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        If lastRow < 2 Then lastRow = 2
        Set Rng1 = .Range("B2:B" & lastRow)
    End With
    
    ' --------------------------
    ' 这里写你的核心业务逻辑
    ' 示例：将范围数据读入数组处理
    Dim dataArr As Variant
    dataArr = Rng1.Value
    
    ' 对数组进行操作（比直接操作单元格快100倍以上）
    ' ...
    ' --------------------------
    
Cleanup:
    ' 恢复原有设置
    With Application
        .ScreenUpdating = True
        .Calculation = originalCalc
        .EnableEvents = True
    End With
    
    ' 错误处理
    If Err.Number <> 0 Then
        MsgBox "代码执行出错：" & Err.Description, vbCritical
        Err.Clear
    End If
End Sub

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