Collapsing groups in Macro

C

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
 
R

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

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

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

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

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top