VBA to Collapse Groups

[Rhys]

Hi,

Part of a macro that I have written groups several sets of columns together (with the nice + above the columns!). I recorded this part of the macro, but excel doesnt record my actions when I collapse the groups to hide the grouped columns. Can anybody suggest some VBA?

Thanks in advance...

 

[Andrew Poulsom]

Try these:

Sub Collapse()
    Worksheets("Sheet1").Columns("A").ShowDetail = False
End Sub

Sub Expand()
    Worksheets("Sheet1").Columns("A").ShowDetail = True
End Sub

where column A is the first column in your outline. Change the Sheet reference to suit.

 

[tusharm]

Check ShowLevels in XL VBA help. It is used as:

    Worksheets(3).Outline _
        .ShowLevels rowLevels:=3, columnLevels:=2

 

[Rhys]

Thanks a lot. Both methods work a treat