Command Button to Hide/Unhide Rows

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

I need to expand only the rows listed (a4, a5, a20, a35, a50) but 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"

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



Bea wrote:
>
> In a MS discussion board a few months ago, someone helped me with the
> macro listed below. It has worked like a charm and I have been able to
> replicate it for different workbooks. I am now stuck again though.
>
> I need to expand only the rows listed (a4, a5, a20, a35, a50) but
> 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"
>
> --
> Bea
> ------------------------------------------------------------------------
> Bea's Profile: http://www.excelforum.com/member.php...o&userid=32500
> View this thread: http://www.excelforum.com/showthread...hreadid=522901

--

Dave Peterson

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 wrote:
> In a MS discussion board a few months ago, someone helped me with the
> macro listed below. It has worked like a charm and I have been able to
> replicate it for different workbooks. I am now stuck again though.
>
> I need to expand only the rows listed (a4, a5, a20, a35, a50) but
> 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"
>
>

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?

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