excel - 如何在VBA中加快每个循环的速度？

我有一个Worksheet_Change宏，它根据用户在带有数据验证列表的单元格中所做的选择来隐藏/取消隐藏行。

该代码需要一分钟才能运行。它遍历了约2000行。我希望它花费近几秒钟，以便它成为有用的用户工具。

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
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    'Declare Variables
    Dim rng_DropDown As Range
    Dim rng_HideFormula As Range
    Dim rng_Item As Range

    'The reference the row hide macro will look for to know to hide the row
    Const str_HideRef As String = "Hide"

    'Define Variables
    'The range that contains the week selector drop down
    Set rng_DropDown = Range("rng_WeekSelector")
    'The column that contains the formula which indicates if a row should
    'be hidden c.2000 rows
    Set rng_HideFormula = Range("rng_HideFormula")

    'Working Code
    'Exit sub early if the Month Selector was not changed
    If Not Target.Address = rng_DropDown.Address Then GoTo EExit

    'Otherwise unprotect the worksheet
    wks_DailyPlanning.Unprotect (str_Password)

    'For each cell in the hide formula column
    For Each rng_Item In rng_HideFormula

        With rng_Item
            'If the cell says "hide"
            If .Value2 = str_HideRef Then

                'Hide the row
                .EntireRow.Hidden = True

            Else
                'Otherwise show the row
                .EntireRow.Hidden = False

            End If
        End With
    'Cycle through each cell
    Next rng_Item

    EExit:
    'Reprotect the sheet if the sheet is unprotected
    If wks_DailyPlanning.ProtectContents = False Then wks_DailyPlanning.Protect (str_Password)


    'Clear Events
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

我查看了该网站上其他用户提供的一些链接，我认为麻烦在于我必须逐一遍历每一行。

是否可以创建类似.visible设置的数组，我可以一次将其应用于整个范围？

最佳答案

另一种可能性：

Dim mergedRng As Range

'.......

rng_HideFormula.EntireRow.Hidden = False
For Each rng_Item In rng_HideFormula
    If rng_Item.Value2 = str_HideRef Then
        If Not mergedRng Is Nothing Then
            Set mergedRng = Application.Union(mergedRng, rng_Item)
        Else
            Set mergedRng = rng_Item
        End If
    End If
Next rng_Item
If Not mergedRng Is Nothing Then mergedRng.EntireRow.Hidden = True
Set mergedRng = Nothing

'........

关于excel - 如何在VBA中加快每个循环的速度？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58304413/