Hiding multiple non-contiguous rows simultaneously and quickly

[AIDSNGO]

Hi All,

I have a spreadsheet (too large to upload) that has a long series of questions in it with yes or no ActiveX radio buttons next to them. When they are marked "no" I have written the code so that the rows between it and the next question hide.

This all works fine, but I have another Sub that unhides the first two rows of each question, but not these rows that have been hidden by the radio button. Unfortunately, given the number of questions that I have in the spreadsheet, having a series of steps that unhides each section separately is really bogging down the spreadsheet and making it run very slowly. Is there a way to have a series of non-contiguous rows all unhide in one process? Or some other way to speed this process up? I tried using something like


Rows("302:303,310:311").EntireRow.Hidden = False

But I get the error message "Run-time error '13': Type mismatch.

Help?

A sample of the VBA:



Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
Rows("208:213").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton4_Click()
If OptionButton4.Value = True Then
Rows("208:213").EntireRow.Hidden = False
End If
End Sub

Private Sub OptionButton5_Click()
If OptionButton5.Value = True Then
Rows("216:220").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton6_Click()
If OptionButton6.Value = True Then
Rows("216:220").EntireRow.Hidden = False
End If
End Sub

Private Sub OptionButton7_Click()
If OptionButton7.Value = True Then
Rows("223:227").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton8_Click()
If OptionButton8.Value = True Then
Rows("223:227").EntireRow.Hidden = False
End If
End Sub

Private Sub OptionButton17_Click()
If OptionButton17.Value = True Then
Rows("230:233").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton18_Click()
If OptionButton18.Value = True Then
Rows("230:233").EntireRow.Hidden = False
End If
End Sub

Private Sub OptionButton19_Click()
If OptionButton19.Value = True Then
Rows("236:240").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton20_Click()
If OptionButton20.Value = True Then
Rows("236:240").EntireRow.Hidden = False
End If
End Sub

Private Sub OptionButton21_Click()
If OptionButton21.Value = True Then
Rows("243:246").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton22_Click()
If OptionButton22.Value = True Then
Rows("243:246").EntireRow.Hidden = False
End If
End Sub

Private Sub OptionButton23_Click()
If OptionButton23.Value = True Then
Rows("249:254").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton24_Click()
If OptionButton24.Value = True Then
Rows("249:254").EntireRow.Hidden = False
End If
End Sub


If target.Address = "$L$190" Then
Select Case Worksheets("Instructions and Worksheet").Range("L190").Value
Case "0"
Rows("203:477").EntireRow.Hidden = True
Case "1"
Rows("257:477").EntireRow.Hidden = True
Rows("203:207").EntireRow.Hidden = False
Rows("214:215").EntireRow.Hidden = False
Rows("221:222").EntireRow.Hidden = False
Rows("228:229").EntireRow.Hidden = False
Rows("234:235").EntireRow.Hidden = False
Rows("241:242").EntireRow.Hidden = False
Rows("247:248").EntireRow.Hidden = False
Rows("255:256").EntireRow.Hidden = False
Case "2"
Rows("312:477").EntireRow.Hidden = True
Rows("203:207").EntireRow.Hidden = False
Rows("214:215").EntireRow.Hidden = False
Rows("221:222").EntireRow.Hidden = False
Rows("228:229").EntireRow.Hidden = False
Rows("234:235").EntireRow.Hidden = False
Rows("241:242").EntireRow.Hidden = False
Rows("247:248").EntireRow.Hidden = False
Rows("255:256").EntireRow.Hidden = False
Rows("257:262").EntireRow.Hidden = False
Rows("269:270").EntireRow.Hidden = False
Rows("276:277").EntireRow.Hidden = False
Rows("283:284").EntireRow.Hidden = False
Rows("289:290").EntireRow.Hidden = False
Rows("296:297").EntireRow.Hidden = False
Rows("302:303").EntireRow.Hidden = False
Rows("310:311").EntireRow.Hidden = False

[Kenneth Hobs]

Use Areas. e.g.


Sub t()
Dim a As Areas, v As Variant
Set a = Range("2:3,10:11").Rows.Areas
For Each v In a
v.EntireRow.Hidden = False
Next v
End Sub

[geekgirlau]

Something to consider with the structure you've chosen ...

Personally (and I'm sure I'm not the only one) I'm very keyboard oriented - I seriously dislike a layout that forces me to grab the mouse. When you add a radio button onto a worksheet, you cannot use the keyboard to activate the control. It also means that you must have code attached to each control.

If you want to do this on a worksheet, have you thought about just using a data validation list in the cell offering "yes" and "no" as the only valid options? The user can tab through the sheet, and as you're working with cells (rather than objects floating above the sheet) you can simplify your code dramatically by working with ranges relative to the position of the cell.

[Bob Phillips]

You can also simplify the optionbutton code


Private Sub OptionButton3_Click()
Rows("208:213").Hidden = OptionButton3.Value
End Sub

Private Sub OptionButton4_Click()
Rows("208:213").Hidden = Not OptionButton4.Value
End Sub

Private Sub OptionButton5_Click()
Rows("216:220").Hidden = OptionButton5.Value
End Sub

Private Sub OptionButton6_Click()
Rows("216:220").Hidden = Not OptionButton6.Value
End Sub

Private Sub OptionButton7_Click()
Rows("223:227").Hidden = OptionButton7.Value
End Sub

Private Sub OptionButton8_Click()
Rows("223:227").Hidden = Not OptionButton8.Value
End Sub

Private Sub OptionButton17_Click()
Rows("230:233").Hidden = OptionButton17.Value
End Sub

Private Sub OptionButton18_Click()
Rows("230:233").Hidden = OptionButton18.Value
End Sub

Private Sub OptionButton19_Click()
Rows("236:240").Hidden = OptionButton19.Value
End Sub

Private Sub OptionButton20_Click()
Rows("236:240").Hidden = Not OptionButton20.Value
End Sub
Private Sub OptionButton21_Click()
Rows("243:246").Hidden = OptionButton21.Value
End Sub

Private Sub OptionButton22_Click()
Rows("243:246").Hidden = Not OptionButton22.Value
End Sub

Private Sub OptionButton23_Click()
Rows("249:254").Hidden = OptionButton23.Value
End Sub

Private Sub OptionButton24_Click()
Rows("249:254").Hidden = Not OptionButton24.Value
End Sub


But why use two option buttons for Yes/No, one checkbox does the same job.

[AIDSNGO]

Thanks all, those are all very helpful comments! @xld - simplifying the option button code definitely makes sense. @geekgirlau - good thought on the validated cells--definitely preferable in most cases.

@Kenneth Hobbs - that simplified my code a lot.

However, Excel still takes a long time to execute all of that code. If anyone has an idea for allowing me to hide and unhide a string of noncontiguous rows without it bogging everything down, that would be amazing!

Thanks,

AIDSNGO

[Kenneth Hobs]

Use Application.Calculation and Application.ScreenUpdating to speed things up. I do that so often that I wrote a kb article with code to do it. http://vbaexpress.com/kb/getarticle.php?kb_id=1035

You might want to breakout your routine into a Sub so that you can pass some parameters to it to shorten the code a bit.

[AIDSNGO]

Thanks again, Kenneth!

I pasted that code into a new module, but it doesn't seem to be working any differently. Is there anything else I need to do?

Thanks,

Julien

[Kenneth Hobs]

It is best to insert the SpeedUp Module and then call the routines.

In a Module:

Sub OBttnHide(sRows As String, Optional tfHide As Boolean = True)
Dim a As Areas, v As Variant
On Error GoTo EndSub
SpeedOn
Set a = Range(sRows).Rows.Areas
For Each v In a
v.EntireRow.Hidden = tfHide
Next v
EndSub:
SpeedOff
End Sub


In the sheet code:

Private Sub OptionButton1_Click()
OBttnHide "2:3,5:5,9:10"
End Sub

Private Sub OptionButton2_Click()
OBttnHide "2:3,5:5,9:10", False
End Sub