Collapsing groups in Macro

[Cunning_Plan]

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

 

[ryguy7272]

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---

 

[Cunning_Plan]

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]

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---

 

[Cunning_Plan]

" 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]

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---

 

[Cunning_Plan]

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...

 

[James Ninni]

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 toset
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...










- Show quoted text -

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