Hiding rows is slow. Is there a code error?

[barkie]

I have a main sheet in a workbook in which I use three toggle buttons to hide different combinations of rows. I also have a reset button to reshow all rows. The sheet has many links to other sheets and consequently hiding the rows takes up to about 90 seconds. (If I remove the links, hiding rows is almost instantaneous).<o></o>
The reset button is also almost instantaneous (even with all the links in place).<o></o>
When I click on one of the toggle buttons the rows hide in a couple of seconds but the “eggtimer” shows for the other 90, or so, seconds and I cannot perform any other operations on the worksheet until it goes away.<o></o>
I have noticed however, that if I press escape, as soon as the rows are hidden (eg after two seconds) I get a dialogue box saying “code execution has been interrupted”, I can press “end”, and I have got my rows hidden within just a few seconds. I can reset the rows and re-hide them this way as many times as I like with no apparent “ill effects”.<o></o>
This makes me think that the code is doing something unnecessary for at least 80 of the 90 seconds.<o></o>
I have uploaded the code for one of the 3 toggle buttons, and for the “reset” button.<o></o>
Is there something wrong with the code?<o></o>
<o> </o>

Private Sub ToggleButton3_Click()

  'this resets all rows first and then hides staff rows

  Call ToggleButton4_Click
  Dim myRange1 As Range, myRange2 As Range, myRange3 As Range, myRange As Range
      Set myRange1 = Range("7:8,10:11,13:14,16:17,19:20,22:23,25:26,28:29,31:32,34:35,37:38,40:41,43:44,46:47,49:50,52:53,55:56,58:59,61:62,64:65,68:69,74:74")
      Set myRange2 = Range("81:82,84:85,87:88,90:91,93:94,96:97,99:100,102:103,105:106,108:109,111:112,114:115,117:118,120:121,123:124,126:127,129:130,132:133,135:136,138:139,142:143,148:148")
      Set myRange3 = Range("155:156,158:159,161:162,164:165,167:168,170:171,173:174,176:177,179:180,182:183,185:186,188:189,191:192,194:195,197:198,200:201,203:204,206:207,209:210,212:213,216:218")
      Set myRange = Union(myRange1, myRange2, myRange3)
          myRange.Rows.Hidden = Not myRange.Rows.Hidden
  End Sub '--


  Private Sub ToggleButton4_Click()

  'this resets all rows

  Range("7:220").Rows.Hidden = Not Range("7:220").Rows.Show
   
  End Sub

<o></o>

 

[carsten]

I'm not a guru and havn't tested it, but give it a try:

rivate Sub ToggleButton3_Click()
  'this resets all rows first and then hides staff rows
  Call ToggleButton4_Click
  Dim myRange1 As Range, myRange2 As Range, myRange3 As Range, myRange As Range
      Set myRange1 = Range("7:8,10:11,13:14,16:17,19:20,22:23,25:26,28:29,31:32,34:35,37:38,40:41,43:44,46:47,49:50,52:53,55:56,58:59,61:62,64:65,68:69,74:74")
      Set myRange2 = Range("81:82,84:85,87:88,90:91,93:94,96:97,99:100,102:103,105:106,108:109,111:112,114:115,117:118,120:121,123:124,126:127,129:130,132:133,135:136,138:139,142:143,148:148")
      Set myRange3 = Range("155:156,158:159,161:162,164:165,167:168,170:171,173:174,176:177,179:180,182:183,185:186,188:189,191:192,194:195,197:198,200:201,203:204,206:207,209:210,212:213,216:218")
      Set myRange = Union(myRange1, myRange2, myRange3)
          myRange.Rows.Hidden = True
  End Sub '--
 
  Private Sub ToggleButton4_Click()
  'this resets all rows
  Range("7:220").Rows.Hidden = False
   
  End Sub

 

[barkie]

Thanks for that.
It didn't solve the problem but the logic is much better. I think I've sorted it now, but incorporated your code, as well.
I have stopped automatic calculations (ie formatting and links) until the code has been executed, and then reinstated the calculations at the end (see revised code below).
Thanks once again
Barkie

Private Sub ToggleButton3_Click()
'show/hidestaff
Application.Calculation = xlCalculationManual
Call ToggleButton4_Click
Dim myRange1 As Range, myRange2 As Range, myRange3 As Range, myRange As Range
    Set myRange1 = Range("7:8,10:11,13:14,16:17,19:20,22:23,25:26,28:29,31:32,34:35,37:38,40:41,43:44,46:47,49:50,52:53,55:56,58:59,61:62,64:65,68:69,74:74")
    Set myRange2 = Range("81:82,84:85,87:88,90:91,93:94,96:97,99:100,102:103,105:106,108:109,111:112,114:115,117:118,120:121,123:124,126:127,129:130,132:133,135:136,138:139,142:143,148:148")
    Set myRange3 = Range("155:156,158:159,161:162,164:165,167:168,170:171,173:174,176:177,179:180,182:183,185:186,188:189,191:192,194:195,197:198,200:201,203:204,206:207,209:210,212:213,216:216")
    Set myRange = Union(myRange1, myRange2, myRange3)
        myRange.Rows.Hidden = True
   Application.Calculation = xlCalculationAutomatic
End Sub '--

Private Sub ToggleButton4_Click()
'reset
Range("7:220").Rows.Hidden = False

End Sub

 

[jbeaucaire]

Without know what all else is going on in your sheet, perhaps hiding the rows is somehow causing a massive recalculation in your sheet. Adding in some global disabling of other code/calulcation at the top of the macro might help, turn it back on when done.

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

 (your macro ...)

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

EDIT: Ah, I see you came to the same conclusion...cheers.