VBA code to ungroup and regroup columns

celkins

New Member
Joined
Jan 14, 2008
Messages
11
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Welcome to the board.

I believe this will do what you want.
Code:
Sub GroupTest()
        Columns("B:C").Columns.Ungroup
        ActiveSheet.PrintOut
        Columns("B:C").Columns.Group
End Sub
Change column references as needed.
 
Upvote 0
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
Code:
    ActiveSheet.Outline.ShowLevels ColumnLevels:=2
    ActiveSheet.PrintOut
    ActiveSheet.Outline.ShowLevels ColumnLevels:=1
 
Upvote 0
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?
 
Upvote 0
For future reference to anyone stumbling on this post, here's a solution to expanding & un-expanding groups of rows & columns.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top