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