Expanding only selected rows in Excel using VBA Macros

Question

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!

Solution

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

Comments

[Raystafarian]

I'm having trouble understanding the question. You have groups of rows where a cell will be "yes" or "no" and you want it to expand that group, or you want it to show the last line of the group?