Show Details of All Groups? 1

[DD999]

I've got a range defined that includes a series of groups that were created manually. I'd like to be able to Show Details and Hide Details for all of the groups in the range (or in the entire worksheet) at once.

Does anybody know a fast way to do this in VBA code?

I'll be using it in a function but also want to assign it to a macro as I can't see any menu commands that do this.

Funny thing is I tried using the macro builder with the Data/Group and Outline/Show Detail command but the macro failed when I tried using it saying I was passing too many parameters.

 

[GlennUK]

What about using the outline symbols? ( If these aren't showing, use menu command Tools/Options/View and tick the Outline Symbols checkbox ).

The outline numbers are at the top left of the spreadsheet, and clicking on the greatest outline number shows all levels of detail. Clicking on outline number 1 hides all levels of details for all groups.

Cheers, Glenn.

 

[DD999]

Hi Glenn,

Thanks for the Outline tip. I actually had it turned on, just never noticed it before.

Now do you know how I could accomplish the same thing in VBA code?

I need to create some buttons that do similar things.

Thanks again.

 

[DD999]

I figured out how to do the same thing in VBA code. The following seems to work:

ActiveSheet.Outline.ShowLevels RowLevels:=4

So my final wrinkle is: If I have two ranges named and I have groups created in each range, is there any way to only open the groups for one particular range? Is there a way to specify just the range name instead of "ActiveSheet".

Sorry for the basic level question. I'm just learning how to use VBA in Excel.

 

[GlennUK]

Hi again,

you could have code similar to this to do the same thing:

Sub TopLevel()
    ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End Sub
Sub LowLevel()
    ActiveSheet.Outline.ShowLevels RowLevels:=4, ColumnLevels:=4
End Sub

These examples are for a sheet that has 4 levels of outlining for columns and rows, you may need to alter them to suit your circumstances.

If you need to control the showing or hiding or detail for a particular group by VBA code then let me know, and I'll post an example ( it's tougher than you think actually ).

Cheers, Glenn.

 

[GlennUK]

Hi again,

we must have posted at the same time. Here's how you control detail being shown for a particular column :

   ExecuteExcel4Macro "SHOW.DETAIL(2,5,TRUE,)"

The arguments are as follows, the first ( 2 in the example ) determines whether the command is intended for rows or columns, 1=rows,2=columns.

The second argument is the position of the outline symbol, so in the example the outline symbol above column 5 ( column E ) is the one being controlled.

The third argument is whether to hide or unhide the outline, TRUE = Unhide, FALSE = Hide.

Hope that helps.
Cheers, Glenn.

 

[DD999]

Hi Again,

Yes I would really benefit from knowing how to hide and show details for a particular group.

As well, how about that question of showing and hiding for all groups in a named range? Is that possible.

Thanks again, Glenn

 

[GlennUK]

Hi there,

here is blunderbus approach, which issues the SHOW.DETAIL command for each row in a named range:

    For Each r In ActiveSheet.Range("FirstRange").Rows
        ExecuteExcel4Macro "SHOW.DETAIL(1," & r.Row & ",FALSE,)"
    Next

I hope this is fast enough ( with a large number of rows the response may not be so good ).

Cheers, Glenn.

 

[DD999]

That should work pretty well for what I need.

I hope to control the number of rows through optimizing the design of the data storage.

Thanks for sharing your knowledge.

Cheers