excel - Programmatically group and ungroup rows or columns -

excel - Programmatically group and ungroup rows or columns -

is there way programmatically grouping / ungroup columns or rows in excel 2010?

note:

the "manual" command in info > outline > grouping / ungroup in excel 2003, used work: somesheet.columns(i).showdetail = true / false not work longer in 2010 groups (only pivot tables , subtotals groupings) recording macro not produce code use

more precisely, calling myrange.showdetail = true in excel 2010 expand collapsed group, raises error if grouping expanded. , showdetail property returns true, whether grouping expanded or not.

regarding rows not in pivot tables ... has not been experience in excel 2010 showdetail evaluates true. thought did didn't realize needed on summary row property work expected. sec of all, didn't realize summary row default under grouped rows. testing collapsed/expanded became much clearer 1 time changed setting have summary row above grouped rows (in ribbon: info > outline, show outline dlg box).

if selected cell on summary row, showdetail evalutes true if grouped records showing, , false if not. key me beingness on summary row see behavior work way. having child/grouped rows above default threw me.

here's macro, dynamically expands , collapses grouped records tied summary row when select cell on summary row. and, makes cell in column bold if section expanded. macro not run if i've selected more 1 cell.

note worksheet protection prevents expanding , collapsing groups of cells. worksheet protected, unprotect sheets expand/collapse reprotect them after. (a possible improvement me unprotect/protect current sheet instead of of them.)

private sub worksheet_selectionchange(byval target range) 'toggle show/hide row if target.cells.count = 1 if (target.entirerow.outlinelevel = 1) , (target.offset(1, 0).entirerow.outlinelevel = 2) , _ (target.column < 15) phone call macros.protshts(false) target.entirerow.showdetail = not target.entirerow.showdetail if target.entirerow.showdetail = true range(cells(target.row, 1), cells(target.row, 14)).font.bold = true else range(cells(target.row, 1), cells(target.row, 14)).font.bold = false end if phone call macros.protshts(true) end if end if end sub

remember, set summary row above grouped records. if summary row below grouped records (the default) offset row reference must changed -1, this:

(target.offset(1, 0).entirerow.outlinelevel = 2)

excel vba excel-vba excel-2010