如何加速VBA中Worksheet_Change宏的For Each循环？

Hey there! You’re exactly right—looping through every single row one by one is the main reason your macro is dragging its feet. Each time you interact with an Excel cell (like checking .Value2 or setting .EntireRow.Hidden), there’s overhead, and that adds up fast with 2000 rows. Let’s rewrite this to use batch operations and array logic to cut runtime down to just a few seconds.

The Core Fix: Batch Row Operations Instead of Iterating #

Instead of checking each row individually, we’ll:

1. Pull all the "Hide/Show" values from your formula range into a memory array (way faster than reading cells one by one).
2. Collect all rows that need hiding into a single range using Union().
3. Collect all rows that need showing into another single range.
4. Set the Hidden property for both ranges in one go (only two Excel interactions instead of 2000!).

Optimized Code #

Here’s the revised version of your macro with key improvements:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    'Exit the routine early if there is an error
    On Error GoTo EExit
    'Manage Events & Speed Settings
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual 'Add this to prevent unnecessary recalculations
    
    'Declare Variables
    Dim rng_DropDown As Range
    Dim rng_HideFormula As Range
    Dim arr_HideValues As Variant
    Dim rng_Hide As Range
    Dim rng_Show As Range
    Dim i As Long
    
    'Constants
    Const str_HideRef As String = "Hide"
    
    'Define Variables
    Set rng_DropDown = Range("rng_WeekSelector")
    Set rng_HideFormula = Range("rng_HideFormula")
    arr_HideValues = rng_HideFormula.Value2 'Pull all values into an array
    
    'Exit sub early if the Week Selector was not changed
    If Not Target.Address = rng_DropDown.Address Then GoTo EExit
    
    'Unprotect the worksheet
    wks_DailyPlanning.Unprotect (str_Password)
    
    'Loop through the array (memory-based, super fast)
    For i = 1 To UBound(arr_HideValues, 1)
        If arr_HideValues(i, 1) = str_HideRef Then
            'Add row to hide range
            If rng_Hide Is Nothing Then
                Set rng_Hide = rng_HideFormula.Cells(i, 1).EntireRow
            Else
                Set rng_Hide = Union(rng_Hide, rng_HideFormula.Cells(i, 1).EntireRow)
            End If
        Else
            'Add row to show range
            If rng_Show Is Nothing Then
                Set rng_Show = rng_HideFormula.Cells(i, 1).EntireRow
            Else
                Set rng_Show = Union(rng_Show, rng_HideFormula.Cells(i, 1).EntireRow)
            End If
        End If
    Next i
    
    'Batch update hidden status (only 2 Excel operations!)
    If Not rng_Hide Is Nothing Then rng_Hide.Hidden = True
    If Not rng_Show Is Nothing Then rng_Show.Hidden = False
    
EExit:
    'Cleanup & Reset Settings
    If wks_DailyPlanning.ProtectContents = False Then
        wks_DailyPlanning.Protect (str_Password)
    End If
    Application.Calculation = xlCalculationAutomatic 'Restore calculation mode
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    
    'Release object variables
    Set rng_DropDown = Nothing
    Set rng_HideFormula = Nothing
    Set rng_Hide = Nothing
    Set rng_Show = Nothing
End Sub

Key Improvements Explained #

• Array-Based Value Reading: arr_HideValues = rng_HideFormula.Value2 reads all values from your formula range into a memory array in one step. Accessing array elements is almost instantaneous compared to reading cells.
• Batch Range Collection: Using Union() to group all rows that need the same Hidden status means we only interact with Excel twice (once to hide, once to show) instead of 2000 times.
• Manual Calculation: Adding Application.Calculation = xlCalculationManual prevents Excel from recalculating formulas during the macro run, which can save extra time if your sheet has complex formulas.
• Clean Object Release: Explicitly setting object variables to Nothing helps free up memory, which is a good practice for VBA macros.

Extra Tips for Even Better Performance #

• Ensure rng_HideFormula is a continuous range (no blank rows in between). Union() works fastest with contiguous ranges, but even non-contiguous will still be way faster than your original loop.
• If your "Hide" formula depends on the week selector value, you can add rng_HideFormula.Calculate right after changing the week selector to make sure values are up to date before processing.
• Test without sheet protection first to confirm speed gains, then re-enable protection once you’re satisfied.

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