Hide rows in Excel based on two values

I am trying to hide rows in Excel between a row with value "P1" in column B and a row with value "P2" also in column B.

I used ChatGPT for a part.

The code hides the rows, but I was not able to make them reappear when I push the button. Some of the things I tried are commented out.

Private Sub CommandButton1_Click()

    Dim ws As Worksheet
    Dim startRow As Long
    Dim endRow As Long
    Dim cell As Range
    Dim Rng As Range
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Calculatie")
    
    ' Find the start and end rows based on values in column B
    For Each cell In ws.Range("B:B")
        If cell.Value = "P1" And startRow = 0 Then
            startRow = cell.Row
        ElseIf cell.Value = "P2" And startRow <> 0 Then
            endRow = cell.Row
            Exit For
        End If
    Next cell
    
    'Set Rng = Selection
    
    'Set Rng = ws.Rows(startRow + 1 & ":" & endRow - 1)
    'Set Rng = ws.Range("startRow", "endRow")
    
    
    ' Hide rows between startRow and endRow
    If startRow > 0 And endRow > 0 Then
    'If Rng.Hidden = True Then
        'Selection.EntireRow.Hidden = True
        ws.Rows(startRow + 1 & ":" & endRow - 1).EntireRow.Hidden = True
    Else
        'Selection.EntireRow.Hidden = False
        'ws.Rows(startRow + 1 & ":" & endRow - 1).EntireRow.Hidden = False
        MsgBox "Start or end value not found in column B"
    End If
End Sub

I tried to make a range and change the hidden value, gave errors.

Solution

If I understand you correctly, you want to toggle the visibility of the rows: First click on the button will hide them, next click will show them again.

To unhide hidden rows, just set the property Hidden to False.

So what you need is to check if the rows are already hidden. The following snippet will do that: It saves the Hidden-property of the first row below "P1" into a variable (isAlreadyHidden) and set the Hidden-Property of the rows to the opposite (Not isAlreadyHidden)

 If startRow > 0 And endRow > 0 Then
    Dim isAlreadyHidden As Boolean
    isAlreadyHidden = ws.Rows(startRow + 1).Hidden
    ws.Rows(startRow + 1 & ":" & endRow - 1).EntireRow.Hidden = Not isAlreadyHidden
Else
    MsgBox "Start or end value not found in column B"
End If