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

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.Hidd en = 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?


--
Bea
------------------------------------------------------------------------
Bea's Profile: http://www.excelforum.com/member.php...o&userid=32500
View this thread: http://www.excelforum.com/showthread...hreadid=522901

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


--
Bea
------------------------------------------------------------------------
Bea's Profile: http://www.excelforum.com/member.php...o&userid=32500
View this thread: http://www.excelforum.com/showthread...hreadid=522901