How can I speed up this For Each loop in VBA?

Question

I have an Worksheet_Change macro that hides/unhides rows depending on the choice a user makes in a cell with a data validation list.

The code takes a minute to run. It's looping over c.2000 rows. I'd like it to take closer to a few seconds so it becomes a useful user tool.

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

I have looked at some links provided by other users on this website and I think the trouble lies in the fact I'm having to iterate through each row individually.

Is it possible to create something like an array of .visible settings I can apply to the entire range at once?

You may benefit from [Understanding Read/ Write Speeds](https://stackoverflow.com/questions/53987345/speed-up-excel-vba-copying-data-from-one-sheet-to-another) from hard disk vs memory. — Dean, Oct 09 '19 at 12:56
if you have the result of your visible calculations in a (hidden) column on the row, then apply a `.autofilter` to all the false values in one go. It would be much faster than looping over all of them. — Plutian, Oct 09 '19 at 13:04
I like the idea of an `AutoFilter`, or else go loop through an array and use `Union` to create two range object to hide/unhide all at once. That would be faster than to iterate over items and hide/unhide rows one by one. Futhermore, working code might be better suited for [Code Review](https://codereview.stackexchange.com/) instead =) — JvdV, Oct 09 '19 at 13:06
Also if you need the rows hidden instead of filtered (there is a small difference) then this can be done with an `.advancedfilter`. [Shameless self plug](https://stackoverflow.com/questions/58232543/advanced-filter-in-vba-excel-with-combobox/58233316#58233316) — Plutian, Oct 09 '19 at 13:10

score 3 · Answer 1 · answered Oct 09 '19 at 13:05

I'd suggest copying your data range to a memory-based array and checking that, then using that data to adjust the visibility of each row. It minimizes the number of interactions you have with the worksheet Range object, which takes up lots of time and is a big performance hit for large ranges.

Sub HideHiddenRows()
    Dim dataRange As Range
    Dim data As Variant
    Set dataRange = Sheet1.Range("A13:A2019")
    data = dataRange.Value

    Dim rowOffset As Long
    rowOffset = IIf(LBound(data, 1) = 0, 1, 0)

    ApplicationPerformance Flag:=False

    Dim i As Long
    For i = LBound(data, 1) To UBound(data, 1)
        If data(i, 1) = "Hide" Then
            dataRange.Rows(i + rowOffset).EntireRow.Hidden = True
        Else
            dataRange.Rows(i + rowOffset).EntireRow.Hidden = False
        End If
    Next i
    ApplicationPerformance Flag:=True
End Sub

Public Sub ApplicationPerformance(ByVal Flag As Boolean)
    Application.ScreenUpdating = Flag
    Application.DisplayAlerts = Flag
    Application.EnableEvents = Flag
End Sub

Vasily · Answer 2 · 2019-10-09T13:34:13.323

to increase perfomance you can populate dictionary with range addresses, and hide or unhide at once, instead of hide/unhide each particular row (but this is just in theory, you should test it by yourself), just an example:

Sub HideHiddenRows()
    Dim cl As Range, x As Long
    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")

    x = Cells(Rows.Count, "A").End(xlUp).Row
    For Each cl In Range("A1", Cells(x, "A"))
        If cl.Value = 0 Then dic.Add cl.Address(0, 0), Nothing
    Next cl

    Range(Join(dic.keys, ",")).EntireRow.Hidden = False

End Sub

demo:

score 2 · Accepted Answer · answered Oct 09 '19 at 15:52

2

Another possibility:

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

'........

answered Oct 09 '19 at 15:52

Guest

430
2
4

This was the fastest solution for me. Thank you! – jakrooster Oct 09 '19 at 16:16
@jakrooster. Try it on 50,000 rows to see its real power. :-) – Guest Oct 09 '19 at 16:34

How can I speed up this For Each loop in VBA?

3 Answers3

Linked