How to show and hide grouped columns and rows in Excel using a VBA macro

To show grouped columns or range, the command ExecuteExcel4Macro "SHOW DETAIL" can be used. In short, the syntax can be summarized as is:

ExecuteExcel4Macro "SHOW.DETAIL(row_or_column,location,boolean)"

with

row_or_column = 1 for row, 2 for column
location = integer to indicate row number or column number to show or hide
boolean = True to "Open", False to "Close"

Example: The VBA code below will first "open" the grouped columns and then close the grouped rows.

Sub showdetails()

'Close Columns
ExecuteExcel4Macro "SHOW.DETAIL(2,4,false)"
MsgBox "Grouped columns D to G are ""closed"""
'Open Rows
ExecuteExcel4Macro "SHOW.DETAIL(1,10,true)"
MsgBox "Grouped rows 8 to 11 are ""open"""
End Sub

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com