Command Button to Hide/Unhide Rows

[Bea]

In a MS discussion board a few months ago, someone helped me with th
macro listed below. It has worked like a charm and I have been able t
replicate it for different workbooks. I am now stuck again though.

I need to expand only the rows listed (a4, a5, a20, a35, a50) bu
collapse all rows within the range(a4:a50). How can I accomplish this


"Dim myRng As Range

Set myRng = Me.Range("a4, a5, a20, a35, a50")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden

 

[Dave Peterson]

So you always want rows 6:19, 21:34, 36:49 hidden?

And see 4, 5, 20, 35, 50 when you click a button?

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range
Dim IsHidden As Boolean

Set myRng = Me.Range("a4, a5, a20, a35, a50")
IsHidden = myRng(1).EntireRow.Hidden
Me.Range("a4:a50").EntireRow.Hidden = True
myRng.EntireRow.Hidden = Not (IsHidden)
End Sub

 

[George]

For starters you need a closing bracket and lose the (1)
myRng.EntireRow.Hidden = Not (myRng.EntireRow.Hidden)

The above line will simply hide and unhide those 5 rows
So this works if A4:A50 is already hidden and dont get altered

You could manually hide A4:A50 the first time or add
Worksheets("Sheet1").Range("A4:A50).EntireRow.Hidden = True
To the 'Workbook Open' To hide that range every time the book is opened
Replace "Sheet1" with your sheet name

Otherwise you need a different approach

George

 

[Bea]

I have a sublevel command button as well...

Here is an explanation of what I am trying to do:

I have top level command buttons on a sheet that hide and unhide rows
containing information pertaining to that command button. This
information has a header row and additional rows with details. It also
includes sublevel command button to hide/display these rows with
details.

I want the top-level command button to unhide only the "header" rows to
identify specific items. The user can then use the sublevel command
button to hide or unhide the additional details. When the user clicks
on the top level command button again, I want all the rows to hide
again and not just the header rows.

Currently, they have to click on the sublevel command button first and
then click on the top level.

Does that make sense?

 

[Bea]

I used the macro Dave provided and that worked. Thanks so much!!! :