-1

I have a year progress table, and one of the columns include (+) Sign, what I want is when I click on (+) the rows from number 8 till 15 will drill down the data (Show Data) and the (+) sign in this case will be (-) Sign and, re click on (-) sign to drill up data (Hide data) and the (-) comes (+) again. I want this formula to be applicable in each row of entire table. Note: The problem with my code is only done for one row but i want for all , so is there any simple eay to make for all rows.

The Table when rows is Hidden

The Table when rows is Appeared

Trying Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("O:O")) Is Nothing And Target.Cells.CountLarge = 1 Then

If Range("A8:R15").EntireRow.Hidden = True Then

    Range("A8:R15").EntireRow.Hidden = False

Else
    Range("A8:R15").EntireRow.Hidden = True
End If
End If
End Sub

Update

Screenshot after using the correct Code

Maya
  • 59
  • 6

1 Answers1

0

I's use the double-click event here: otherwise it's a bit awkward needing to click out of the cell and back again to reverse the hide/unhide.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range
    'exit if not in monitored column
    If Intersect(Target, Me.Range("O:O")) Is Nothing Then Exit Sub
    'check for +/-
    If Target.Value = "+" Or Target.Value = "-" Then
        Set rng = Target.Offset(1, 0).Resize(8).EntireRow
        rng.Hidden = Not rng.Hidden
        Target.Value = IIf(rng.Hidden, "+", "-") 'reset cell text
        Cancel = True 'don't enter edit mode in the clicked cell
    End If
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks works properly, but is there any way for drill down data to add rows automatically for each main row, because Iam adding the rows manually row by row for that data. – Maya Jan 21 '21 at 10:20
  • As shown in the image above in update. It tooks the 8 rows from the filled data I already have in the table, not empty 8 rows – Maya Jan 21 '21 at 10:37
  • That seems like a different question from the one you asked - maybe ask that in a new post. – Tim Williams Jan 21 '21 at 16:52