On Jul 22, 5:15*pm, Cunning_Plan
<CunningP...@discussions.microsoft.com> 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 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...
>
>
>
> "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 andwhat 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 numberof
> > > > > mastersheets.
>
> > > > > if there isn't a way to do this I'll have to design around it butyou'd
> > > > > think it would just be a simple function.... or perhaps a defaultsetting 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 becollapsed 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 acustom 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
|
Similar Threads
