Hide rows sub is taking forever

Question

I have a spreadsheet that needs to toggle show/hide rows that are dependent on Column G values.

I have two Buttons:

[Show All] - This will execute a sub to show all rows.
[Hide FALSE] - This will execute a sub to Hide any rows where its value in Column G = FALSE

The problem is that the Sub only needs to run through 250 rows, but it take about 5 minutes to complete the sub. I assure you it has nothing to do with my PC specs.

Excel VBA Code:

'This sub takes about 5 minutes to complete  (WHY?)
Private Sub btnHideFalse_Click()
  Application.ScreenUpdating = False
  Dim i As Integer
  Dim wsSelect As Worksheet
  Set wsSelect = ThisWorkbook.ActiveSheet


  For i = 2 To 250
      'Cells(i,7) point to the current row's column G value
      wsSelect.Rows(i).EntireRow.Hidden = (wsSelect.Cells(i, 7).Value = False)        
  Next i

  Set wsSelect = Nothing

  Application.ScreenUpdating = True

    
End Sub

'This sub works great
Private Sub btnShowAll_Click()
    ThisWorkbook.ActiveSheet.Rows("1:250").EntireRow.Hidden = False
End Sub

Is the value 'False'? `wsSelect.Cells(i, 7).Value = "False"` — Nathan_Sav, Mar 15 '21 at 13:35
@Nathan_Sav lol... you are right. It works now... I can't believe that was the issue — bagofmilk, Mar 15 '21 at 13:37

Absinthe · Answer 1 · 2021-03-15T13:49:41.903

-1

Updating the UI (which hiding rows does) is probably the slowest operation in Excel. As you mentioned doing it in one pass is much quicker, so build the rows to be hidden into a single range. Also, reading cells is quite slow, so read them all at once into an array. Here's an example:

Dim r as range
Dim arr() as variant
    
Set r = ws.Range("A11") ' need an arbitrary row to start the union join below
        
arr = ws.Range("A1:B10").value
    
For x = 1 To 10
   If arr(x, 2) = "MyHideCondition" Then
     Set r = Application.Union(r, ws.Cells(x, 1))
   End If
Next x
        
r.EntireRow.Hidden = True

edited Mar 15 '21 at 13:49

answered Mar 15 '21 at 13:45

Absinthe

3,258
6
31
70

Your explanation is good. Your code, not so much. Especially the `Set r = ws.Range("A11")` (see the helper function in [this answer](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba) for example). "need an arbitrary row to start the union join below" is completely false. Code should not do things to arbitrary rows. – BigBen Mar 15 '21 at 13:48
I upvoted because this seems very interesting. – bagofmilk Mar 15 '21 at 13:49
@BigBen I removed the shorthand code. Please explain the helper function you linked to. – Absinthe Mar 15 '21 at 13:52
Your answer would be better if you incorporated that exact helper function. – BigBen Mar 15 '21 at 13:53
@BigBen It does exactly the same thing! – Absinthe Mar 15 '21 at 13:55
@BigBen `ByVal toCombine As Range`. Think we're done here buddy! – Absinthe Mar 15 '21 at 13:57
2

@Absinthe I don't think you're understanding what that helper function is doing. `If source Is Nothing Then`. There is no need to choose a random row like the current approach with `Set r = ws.Range("A11")`. That's a "hack" and should never be proposed. The linked answer shows how to avoid such a hack. – BigBen Mar 15 '21 at 13:59

Hide rows sub is taking forever

1 Answers1