Using Toggle Button to hide/unhide rows that may change

Question

Hope this makes sense. I'm using Excel 2007 and have created a single-sheet workbook that is used to generate a simple document. There are several sections to the sheet, but I only use some of them to generate the document. So, using the following Toggle Button code, I'm able to hide the sections I don't need, and unhide the ones I do:

Private Sub ToggleButton1_Click()

If ToggleButton1 Then

    Rows("5:20").EntireRow.Hidden = False

Else

    Rows("5:20").EntireRow.Hidden = True

End If

End SubHere's the issue: This is set up to hide/unhide Rows 5 thru 20, and it works great. However, often times I will need to add a bunch of rows. "5:20" will no longer work. I need the Toggle Button's code to automatically update to compensate for the additional rows of data. The problem gets real bad with the Toggle Buttons in the section below the section I've added to. They get quite messy.

Should I use Named Range's to name each section? The name would stay the same, but the number of rows would change?

jp

Answer 1

Thanks!  You were very close.  This is what worked:

 

Private Sub ToggleButton1_Click()

 If ToggleButton1 Then

     Range("A5").EntireRow.Hidden = True

 Else

     Range("A5").EntireRow.Hidden = False

 End If

 End Sub

Hi,

If it did then I'm not sure how it related to your original question because that will only ever hide 1 row and not additional rows as you add more data.

Answer 2

Thanks!  You were very close.  This is what worked:

Private Sub ToggleButton1_Click()

 If ToggleButton1 Then

     Range("A5").EntireRow.Hidden = True

 Else

     Range("A5").EntireRow.Hidden = False

 End If

 End Sub

Answer 3

Hi,

Maybe this

Private Sub ToggleButton1_Click()

If ToggleButton1 Then

    Range("A5").CurrentRegion.EntireRow.Hidden = True

Else

    Range("A5").CurrentRegion.EntireRow.Hidden = False

End If

End Sub