Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

Hi

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.
Thanks
excelmacros-vba
MS Office Version: 2016
posted by: JasonExcel on Mon Mar 12, 2018 at 6:38 am
Answer
Discuss

Discussion

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

Answers

0

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

ActiveSheet.Rows(7).Hidden = True
posted by: Variatus (rep: 4889) on Mon Mar 12, 2018 at 7:59 pm
Discuss

Discussion

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: 4889) 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