How can I group and display columns automatically?

[faraz316]

I have 40 columns on a worksheet and many rows, and want to set up vertical
groupings i.e. group columns (A, D, G, J ....) together, and then (B, E, H,
K... together), and so on - final result is that all columns can be hidden
and each 'group' can be displayed on its own. I know that rows can be grouped
together using the 'Group' function but am not sure of columns. Does anyone
know if this can be done on Excel?

 

[Sean Timmons]

You can group columns just as you would group rows. Highlight the desired
columns, then go to Edit-Group-Group

BUT

You can't group separate columns together...

 

[RagDyer]

XL *only* allows grouping of contiguous rows and columns!

Also, each group must be separated from the next group, on the same level,
by a "summary / total" column or row.

As an excersise, select Columns A & B.
Then hold <Shift>+<Alt> and hit the <Right Arrow>

Select Columns D & E, and do the same thing.

You'll see 2 groupings with the outline symbols over Columns C & F.

NOW, select G & H and group them, THEN, select I & J and group them.

You see how XL combines the two groups into a *single* group.

However, you can individually group single columns, placing them on the same
level.

Group A alone, then C, then E, and finally G.
They're all on the same level.

Now select A to H and group them.

This is as close as you can come to what I think you're looking to
accomplish.

 

[Ken]

faraz

This is the technique use; it may be useful to you.

For each verticle group you want, you create range name for an entire
row somewhere below your data. Put a value in the columns that are to
be part of the group, leve the rest blank. In a column that is always
visible, maybe one of your data columns, maybe a new column solely to
identify your row names. Then in the before double-click event for
the worksheet put in this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim S As String

S = ActiveCell.Value

Range(S).Select

Selection.EntireColumn.Hidden = False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireColumn.Hidden = True

End Sub

You may not want to use the before double-click code to trigger it,
but, the basic idea is you have a row, with non blank values in the
columns you want for your group and the group name visible in a
column. You can add and modify groups by inserting range names for
complete rows without making any VBA changes; which is good if someone
else is adjusting the groups or making up new groups. You can display
the group by double clicking on the group name; or another trigger
action if you prefer.

Good luck

Ken
Nofolk, Va