VBA code to Collapse Specific Range of grouped cells leaving other ranges expanded



Apologies, I am a VBA newbie and this might be a bit basic...

I am looking for some VBA code to collapse a specific range of grouped cells (row 7 on my sheet) whilst leaving other ranges of grouped cells (rows 37 to 51 on my sheet) expanded. I have tried hunting forums but can only find code to collapse all cells (somthing like ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1). So I have attempted using the following but to no avail:-

Rows("7:7").Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

Any help would be greatly appreciated.


I have managed to work it out after a bit more googling. Quite easy really:-

Rows(7).EntireRow.ShowDetail = false
JasonExcel Mar 12, '18 at 6:49 am
Add to Discussion



Why to "collapse" a single row using VBA? Have you considered just hiding the row?

ActiveSheet.Rows(7).Hidden = True


I wanted the finished models to have a 'collapsed' formula row since such a row is more obvious than a hidden row and I wanted the method of updating the sheets (dragging the formulae) to be as obvious as possible to the (non-VBA using) Finance staff responsible for maintining the models on behalf of the users. I take your point though and 'hiding' the row will very often be the better solution.
JasonExcel Mar 21, '18 at 9:24 am
Interesting point. Thank you for sharing.
Variatus (rep: 4392) Mar 21, '18 at 8:02 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login