hide/show rows in more efficient way

Question

What I'm trying to do is to speed up current process. I've got a production plan with 5 weeks looks ahead into. Below is a sample code of a userform which I'm using to determine which rows needs hiding depending on what a user would like to see (for instance if you've got something planned in 4 weeks time and it has its own row, you dont want to see it in 2 weeks view option, so you need to hide this row. Overall code below works great but only on my machine which is quite powerful. Unfortunately ther're a lot of users with much slower PCs and I would like to explore possible actions to speed it up. I read other similar topics and one of the suggestions was to create a helper column and use autofilter. Are there other options?

Sub show2weeks()

    Dim hideRange As Range
    Dim myRow As Range
    Dim wc As Integer
    Dim wr As Range: Set wr = ThisWorkbook.Worksheets("2 week plan").Range("G4:AD4")
    Dim week As String
    week = "week 2"  'zmienic tutaj
Range(Cells(1, 7), Cells(1, 30)).EntireColumn.Select
Selection.EntireColumn.Hidden = False

    Application.ScreenUpdating = False

    wc = wr.Find(what:=week, SearchDirection:=xlPrevious, LookAt:=xlWhole, LookIn:=xlValues).Column

    Application.Calculation = xlCalculationManual

    Set hideRange = Sheets("2 Week plan").Range(Cells(7, 7), Cells(1000, wc)) 'you must set this to apply to the range you want (you could use active selection if you wanted)
    For Each myRow In hideRange.Rows
        If Application.WorksheetFunction.Sum(myRow) = 0 Then 'if the sum of the row=0 then hide
            myRow.EntireRow.Hidden = True
            Else
            myRow.EntireRow.Hidden = False
        End If
    Next

Range(Cells(1, 7), Cells(1, 30)).EntireColumn.Select
Selection.EntireColumn.Hidden = False

Range(Cells(1, wc + 1), Cells(1, 30)).EntireColumn.Select
Selection.EntireColumn.Hidden = True

Application.Calculation = xlCalculationAutomatic

End Sub'''

At risk of being off-topic here (you got working code), it could be a nice post for [code review](https://codereview.stackexchange.com/). I would recommend you post your question over there =) — JvdV, Jan 10 '20 at 08:57
Yes. Using `autofilter` is the fastest way I think. Another is get all the affected rows first then hide in 1 go. Your code will also run much faster if you disable screen updating. — L42, Jan 10 '20 at 09:06
`Select` usualy slows things down, you might want to read [this question](https://stackoverflow.com/q/10714251/8282232). — AntiDrondert, Jan 10 '20 at 09:23

score 0 · Answer 1 · answered Jan 10 '20 at 09:17

Looking at your code, toggling screen updating on/off will further improve performance aside from the calculation. So try:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'// your code here //

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Another is getting all the rows to hide first and then just hide in one go.
In your loop, try getting all the rows first like this:

Dim forHide As Range '// add another variable to hold all the ranges //

For Each myRow In hideRange.Rows
  If Application.WorksheetFunction.Sum(myRow) = 0 Then
    If Not forHide Is Nothing Then
      Set forHide = Union(forHide, myRow) '// get all the ranges together //
    Else
      Set forHide = myRow
    End If
  End If
Next

forHide.EntireRow.Hidden = True '// hide all rows in one go //

These are your 2 other options besides AutoFilter. But if there is a way to implement AutoFilter, that will be the fastest. Hope this helps.

hide/show rows in more efficient way

1 Answers1