# Mac – Expanding only selected rows in Excel using VBA Macros

macrosmicrosoft excelmicrosoft-excel-2007vba

I have written the following VBA code that contains a condition where if the Cell#="Yes" then expand the bottom row, else if Cell#="No" then collapse the bottom row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E15").Value = "Yes" Then
ActiveSheet.Outline.ShowLevels RowLevels:=3
ElseIf Range("E15").Value = "No" Then
ActiveSheet.Outline.ShowLevels RowLevels:=8
End If
End Sub


Now this seems to be working fine without any issues, but for some reason it expands and collapses all rows that are within the same Row Level as displayed in the code, what I really want is to be able to choose exactly which row I want to expand and collapse based on the condition.'

As you can see I'm not much of a VB Pro, but any help would be much appreciated.

Thanks!

If I understand you correctly, you should be able to set and unset the ShowDetails property to expand and collapse specific summary rows in your outline.

As shown below, Showdetails is a read/write property of an entire row (or column) within an outline. To show the detail for a specific summary row, reference a single cell in that row as shown in the example code below. Excel will raise an error if you try to set the property to True for a summary level that is already expanded. If the summary row is not visible because it is collapsed within a larger grouping, Showdetails will (silently) fail to expand the row.

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
If .Range("E15").Value = "Yes" Then
With .Range("A26").EntireRow
If .ShowDetail = False Then
.ShowDetail = True
End If
End With
ElseIf Range("E15").Value = "No" Then
With .Range("A45").EntireRow
If .ShowDetail = False Then
.ShowDetail = True
End If
End With
End If
End With
End Sub