Hiding rows is slow. Is there a code error?

barkie

New Member
Joined
Feb 13, 2009
Messages
48
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:p></o:p>
The reset button is also almost instantaneous (even with all the links in place).<o:p></o:p>
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:p></o:p>
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:p></o:p>
This makes me think that the code is doing something unnecessary for at least 80 of the 90 seconds.<o:p></o:p>
I have uploaded the code for one of the 3 toggle buttons, and for the “reset” button.<o:p></o:p>
Is there something wrong with the code?<o:p></o:p>
<o:p> </o:p>
Code:
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:p></o:p>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

carsten

New Member
Joined
Mar 5, 2009
Messages
15
I'm not a guru and havn't tested it, but give it a try:

Rich (BB code):
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

New Member
Joined
Feb 13, 2009
Messages
48
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

Code:
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.

Code:
    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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,604
Messages
5,765,383
Members
425,281
Latest member
tmoreira001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top