I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson

Bea

There is also ViewsCustom Views if you don't want to go the macro route.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson

Gord - Thanks for your help. Can you use a button with the Custom Views? I
could not figure that one out.

"Gord Dibben" wrote:

Bea

There is also ViewsCustom Views if you don't want to go the macro route.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson

Bea

Not really.

You can go to ToolsCustomizeCommandsView.

Drag the Custom Views drop-down onto your Toolbar.

You can choose which view from the drop-down.

Possible to have many custom views, each with a distinct name like "hidden"
"unhidden" "group1" etc.


Gord

On Tue, 29 Nov 2005 14:01:02 -0800, Bea wrote:

Gord - Thanks for your help. Can you use a button with the Custom Views? I
could not figure that one out.

"Gord Dibben" wrote:

Bea

There is also ViewsCustom Views if you don't want to go the macro route.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson

Sometimes you can just record a macro while you do it manually.

I got this when I showed the view named Test1.

Option Explicit
Sub Macro1()
ActiveWorkbook.CustomViews("test1").Show
End Sub


Behind a commandbutton from the control toolbox toolbar:

Option Explicit
Private Sub CommandButton1_Click()
Me.Parent.CustomViews("test1").Show
End Sub

Me is the worksheet that owns the code (and button). Me.Parent is the workbook.

Bea wrote:

Gord - Thanks for your help. Can you use a button with the Custom Views? I
could not figure that one out.

"Gord Dibben" wrote:

Bea

There is also ViewsCustom Views if you don't want to go the macro route.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson




--

Dave Peterson

Hi

I searched and found also this post helpful. I have an additional question
in this matter.

Is it possible to hide and unhide the rows smoothly as in a Powerpoint?

/Johan




"Bea" skrev:

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson

Maybe...

You can hide rows if you use Data|Subtotals, or Data|Outlining or even
Data|Filter|autofilter.

But it really depends...

Johan wrote:

Hi

I searched and found also this post helpful. I have an additional question
in this matter.

Is it possible to hide and unhide the rows smoothly as in a Powerpoint?

/Johan

"Bea" skrev:

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson


--

Dave Peterson

Is there a way to link the contents of a cell to this macro? For example, I
don't want to have a button, but I want to click the main category text/cell
to either hide or unhide the range specified... Is this possible?

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson

You can hide entire rows or entire columns.

Or you can hide (kind of) a range by giving the a nice format--same color font
as the fill color (white on white???).

Or by using a custom number format:
;;;
(three semicolons)

If you need a macro, you could record one when you do it manually.

Mike wrote:

Is there a way to link the contents of a cell to this macro? For example, I
don't want to have a button, but I want to click the main category text/cell
to either hide or unhide the range specified... Is this possible?

"Dave Peterson" wrote:

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.

--

Dave Peterson


--

Dave Peterson