Dynamically hiding rows with VBA

Question

I have a spreadsheet which hides all rows except those designated by a date and a named region like so:

'Get week no value...
wk = Range("$B$2").Value

'If value changes...
If Target.Address = "$B$2" Then
'Hide all rows/weeks...
Range("allWeeks").Select
Application.Selection.EntireRow.Hidden = True

'...but show week selected by 'wk'
Range(wk).Select
Application.Selection.EntireRow.Hidden = False

All works great. However. Within each named week I have hidden calculation rows defined by "HC" in column A of the row to be hidden. The display of Range(wk) unhides those hidden rows so I introduce a loop to close all the "HC" hidden columns

Dim x As Integer

For x = 1 To 1500
      If Sheet1.Cells(x, 1).Value = "HC" Then
   Sheet1.Rows(x).Hidden = True

    End If
Next

End Sub

The result is that it kinda works but I have to wait several seconds for the process to complete every time I type into a cell which is making the sheet almost unworkable. Any pointers would be appreciated.

Generally you should build up a range of rows that you want to hide and then hide that range separately — Marcucciboy2, Jul 22 '19 at 12:04

Marcucciboy2 · Accepted Answer · 2019-07-22T13:10:54.580

Generally you want to build up a range of rows to hide within the loop and then afterwards hide that range separately. You can build the range to hide using he Union() function like so:

Option Explicit

Sub HideRows()

    Dim mainRng As Range
    Set mainRng = Range("A2:A" & Range("A" & Rows.count).End(xlUp).Row)

    Dim unionRng As Range

    Dim i As Long
    For i = mainRng.Row To mainRng.Row + mainRng.Rows.count - 1
        If Cells(i, 1).Value2 = "HC" Then
            If Not unionRng Is Nothing Then
                Set unionRng = Union(unionRng, Cells(i, 1))
            Else
                Set unionRng = Cells(i, 1)
            End If
        End If
    Next i

    If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True

End Sub

Thanks Marcucciboy2. By positioning all of that code above my own except for the execution line which I put just before my End Sub I got it working after including @A.M. 's Application.Screenupdating suggestion. Thanks again. I bob in your wake. — mikemcmonagle, Jul 22 '19 at 14:20

score 1 · Answer 2 · answered Jul 22 '19 at 13:07

Sometimes when you want to update too many things at once, the UI becomes a bit unresponsive.

I've found that disabling UI updates while doing those changes, speeds things up by an order of magnitude:

Sub XXX()
    ...
    On Error GoTo EH
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.StatusBar = "I'm working on it..."

' do lots of changes in cells, rows, sheets...

' Undo the accel changes:
CleanUp:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.StatusBar = False
Exit Sub
EH:
    ' Do error handling
    MsgBox "Error found: " & Err.Description
    GoTo CleanUp

End Sub

See https://learn.microsoft.com/en-us/office/vba/api/excel.application.screenupdating and Effect of Screen Updating

Dynamically hiding rows with VBA

2 Answers2