VBA code to ungroup and regroup columns

[celkins]

Hi

I am trying to write a macro in excel which opens up a group of columns, prints the normally hidden area, and then closes the group again.

Unfortunately, recording a macro doesn't register this action. Does anyone know the vba code for this process?

Many thanks in advance for your help.

 

[Datsmart]

Welcome to the board.

I believe this will do what you want.

Sub GroupTest()
        Columns("B:C").Columns.Ungroup
        ActiveSheet.PrintOut
        Columns("B:C").Columns.Group
End Sub

Change column references as needed.

 

[Smitty]

Welcome to the Board!

Actually it is recordable, just not pretty.

Here's a condensed method you may be able to adapt:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">With</SPAN> Range("B:D").Columns<br>        .Ungroup<br>            Range("C9:C12").PrintOut<br>        .Group<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[celkins]

Many thanks for your help.

What this code appears to do is remove the grouping but keep the columns hidden. Ideally, I need to expand the grouping to enable sight, and a print, of the data, that is normally not shown.

I am pretty new to vba so thanks for your patience!

 

[Datsmart]

    ActiveSheet.Outline.ShowLevels ColumnLevels:=2
    ActiveSheet.PrintOut
    ActiveSheet.Outline.ShowLevels ColumnLevels:=1

 

[oberghaus]

I have a similar issue - the problem is that in Excel 2003 I can group columns B:M with the Columns("B:M").Columns.Group command. In Excel 2007 the same command, with the same dataset does not work. The issue I can see is that column B also contains the selection area for the page fields. I have created a workaround by inserting the row and data items into the pivot table. Then the VBA groups the columns as I need them and then I insert the page items and filter the information for reporting. Is there a better way to do this?

 

[mathchick]

For future reference to anyone stumbling on this post, here's a solution to expanding & un-expanding groups of rows & columns.