How to Hide/Unhide rows not with grouping

[Patheoff]

Trying to find a way to add some sort of arrow or button that hides and unhides rows like "grouping" but I want to be able to add a title.

For my specific case I have a multiple different items on a Pallet and I was to be able to send out a pallet list in excel but not have it show all the items on the pallet unless you click some sort of drop down

See attached picture, I only want the column that says "Pallet 1" to show but want to add some sort of arrow in the column that will hide/unhide the items below it.

 

[rpaulson]

goto to Developer Menu, Design Mode and add a Cceck box

rightclick on check box and add this code.

Private Sub CheckBox1_Click()
CheckBox1.Caption = "Hidden"
Rows("2:5").Hidden = CheckBox1.Value
End Sub

turn off Design mode and enjoy.

 

[Crystalyzer]

You could use the "BeforeDoubleClick" worksheet event with this code. Every time you double click while in the column containing the Pallet information if the cell that is double clicked on contains the word "Pallet" in it, the rows below it up to but not including the next Pallet will be hidden or unhidden.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim r As Range, r2 As Range, lr As Long
    
        If Target.Column = 5 And Target.Value Like "Pallet*" Then
            Set r = Range(Cells(Target.Row + 1, 5), Cells(Cells(Rows.Count, 5).End(xlUp).Row, 5))
            Set r2 = r.Find("Pallet*")
            If Not r2 Is Nothing Then
                If r2.Row <> Target.Row Then
                    lr = r2.Row - 1
                Else
                    i = 0
                    Do While r.Offset(1 + i, 0).EntireRow.Hidden = True
                        i = i + 1
                    Loop
                    If i = 0 Then
                        lr = Cells(Rows.Count, 5).End(xlUp).Row
                    Else
                        lr = Target.Row + i
                    End If
                End If
            Else
                lr = Cells(Rows.Count, 5).End(xlUp).Row
            End If
            Set r = Range(Cells(Target.Row + 1, 5), Cells(lr, 5))
            If r.EntireRow.Hidden Then
                r.EntireRow.Hidden = False
            Else
                r.EntireRow.Hidden = True
            End If
        End If
End Sub