Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning

Just turn on the macro recorder, select the groupings, hide, link the code to
a Button. Here is a method of hiding colored rows (you may or may not find
it useful):

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.count
If Cells(rr, cl).Interior.ColorIndex < xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning

Whilst that is a useful work around for most of the time it wont work for me
currently. The data set im preparing needs to be formatted very specificly as
its being passed on to a number of people and feeds into a number of
mastersheets.

if there isn't a way to do this I'll have to design around it but you'd
think it would just be a simple function.... or perhaps a default setting in
the master options?



"ryguy7272" wrote:

Just turn on the macro recorder, select the groupings, hide, link the code to
a Button. Here is a method of hiding colored rows (you may or may not find
it useful):

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.count
If Cells(rr, cl).Interior.ColorIndex < xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning

Of course you can program this, or create a script for it. What is the logic?

Selection.Columns.Group expand = false
This doesn't really me anything to me. What do you have now and what do you
expect to see when the macro fires?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Whilst that is a useful work around for most of the time it wont work for me
currently. The data set im preparing needs to be formatted very specificly as
its being passed on to a number of people and feeds into a number of
mastersheets.

if there isn't a way to do this I'll have to design around it but you'd
think it would just be a simple function.... or perhaps a default setting in
the master options?



"ryguy7272" wrote:

Just turn on the macro recorder, select the groupings, hide, link the code to
a Button. Here is a method of hiding colored rows (you may or may not find
it useful):

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.count
If Cells(rr, cl).Interior.ColorIndex < xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning

" Selection.Columns.Group expand = false" was the kind of format i was
looking for not the answer. I have a complex macro grouping a couple of
hundred rows and columns. I need to be able to collapse those columns...


"ryguy7272" wrote:

Of course you can program this, or create a script for it. What is the logic?

This doesn't really me anything to me. What do you have now and what do you
expect to see when the macro fires?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Whilst that is a useful work around for most of the time it wont work for me
currently. The data set im preparing needs to be formatted very specificly as
its being passed on to a number of people and feeds into a number of
mastersheets.

if there isn't a way to do this I'll have to design around it but you'd
think it would just be a simple function.... or perhaps a default setting in
the master options?



"ryguy7272" wrote:

Just turn on the macro recorder, select the groupings, hide, link the code to
a Button. Here is a method of hiding colored rows (you may or may not find
it useful):

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.count
If Cells(rr, cl).Interior.ColorIndex < xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning

Sorry to be thick, i still don't think I'm getting it. This will croup
selected Columns:
Sub rngtest()

Set r = Application.InputBox(prompt:="select range with mouse", Type:=8)
r.Select
r.Columns.Group

End Sub

However, I don't know how useful that code is. Maybe it saves you a couple
clicks on the mouse, but that's it.

Good luck,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

" Selection.Columns.Group expand = false" was the kind of format i was
looking for not the answer. I have a complex macro grouping a couple of
hundred rows and columns. I need to be able to collapse those columns...


"ryguy7272" wrote:

Of course you can program this, or create a script for it. What is the logic?

This doesn't really me anything to me. What do you have now and what do you
expect to see when the macro fires?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Whilst that is a useful work around for most of the time it wont work for me
currently. The data set im preparing needs to be formatted very specificly as
its being passed on to a number of people and feeds into a number of
mastersheets.

if there isn't a way to do this I'll have to design around it but you'd
think it would just be a simple function.... or perhaps a default setting in
the master options?



"ryguy7272" wrote:

Just turn on the macro recorder, select the groupings, hide, link the code to
a Button. Here is a method of hiding colored rows (you may or may not find
it useful):

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.count
If Cells(rr, cl).Interior.ColorIndex < xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning

Grouping rows and columns is easy enough. however, when you group you are
able to to minimise or maximise groups. the thing i want is to be able to set
the groups to start minimised. this would let the end user expand if they
need but if not just get a summary of sorts.

basicly i need the vba command to collapse groups/ minimise groups...




"ryguy7272" wrote:

Sorry to be thick, i still don't think I'm getting it. This will croup
selected Columns:
Sub rngtest()

Set r = Application.InputBox(prompt:="select range with mouse", Type:=8)
r.Select
r.Columns.Group

End Sub

However, I don't know how useful that code is. Maybe it saves you a couple
clicks on the mouse, but that's it.

Good luck,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

" Selection.Columns.Group expand = false" was the kind of format i was
looking for not the answer. I have a complex macro grouping a couple of
hundred rows and columns. I need to be able to collapse those columns...


"ryguy7272" wrote:

Of course you can program this, or create a script for it. What is the logic?

This doesn't really me anything to me. What do you have now and what do you
expect to see when the macro fires?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Whilst that is a useful work around for most of the time it wont work for me
currently. The data set im preparing needs to be formatted very specificly as
its being passed on to a number of people and feeds into a number of
mastersheets.

if there isn't a way to do this I'll have to design around it but you'd
think it would just be a simple function.... or perhaps a default setting in
the master options?



"ryguy7272" wrote:

Just turn on the macro recorder, select the groupings, hide, link the code to
a Button. Here is a method of hiding colored rows (you may or may not find
it useful):

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.count
If Cells(rr, cl).Interior.ColorIndex < xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Cunning_Plan" wrote:

Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning

On Jul 22, 5:15*pm, Cunning_Plan
wrote:
Grouping rows and columns is easy enough. however, when you group you are
able to to minimise or maximise groups. the thing i want is to be able to set
the groups to start minimised. this would let the end user expand if they
need but if not just get a summary of sorts.

basicly i need the vba command to collapse groups/ minimise groups...



"ryguy7272" wrote:
Sorry to be thick, i still don't think I'm getting it. *This will croup
selected Columns:
Sub rngtest()

Set r = Application.InputBox(prompt:="select range with mouse", Type:=8)
r.Select
r.Columns.Group

End Sub

However, I don't know how useful that code is. *Maybe it saves you a couple
clicks on the mouse, but that's it.

Good luck,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"Cunning_Plan" wrote:

" Selection.Columns.Group expand = false" was the kind of format i was
looking for not the answer. I have a complex macro grouping a couple of
hundred rows and columns. I need to be able to collapse those columns....

"ryguy7272" wrote:

Of course you can program this, or create a script for it. *What is the logic?

This doesn't really me anything to me. *What do you have now and what do you
expect to see when the macro fires?

Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"Cunning_Plan" wrote:

Whilst that is a useful work around for most of the time it wont work for me
currently. The data set im preparing needs to be formatted very specificly as
its being passed on to a number of people and feeds into a number of
mastersheets.

if there isn't a way to do this I'll have to design around it but you'd
think it would just be a simple function.... or perhaps a default setting in
the master options?

"ryguy7272" wrote:

Just turn on the macro recorder, select the groupings, hide, link the code to
a Button. *Here is a method of hiding colored rows (you may or may not find
it useful):

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.count
If Cells(rr, cl).Interior.ColorIndex < xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"Cunning_Plan" wrote:

Hi all,

I've been writing a macro at work to re-format a large data set. In order to
make this more readable and to allow for better summaries both the columns
and rows are grouped (allowing them to be expanded or collapsed).

I was wondering if there is away to collapse the groups from within the
macro. I would like the default positions of the groups to be collapsed so
that when the macro finishes all groups are collapsed. something like:

*Selection.Columns.Group expand = false

or some such....

The macro recorder produced no results and using a search term like groups
in a google search produces few useful results!

I would imagine from this i would also then be able to make a custom button
to collapse or expand all groups (unless this is already possible)

I'm using office 97.

Many thanks for any and all comments,

Cunning- Hide quoted text -

- Show quoted text -

ActiveSheet.Outline.ShowLevels RowLevels:=1 ' to collapse the rows
ActiveSheet.Outline.ShowLevels RowLevels:=2 ' to expand the rows