Select and Hide Entire Rows In-Between 2 Named Rows

Question

I have two named rows with information in between that I would like to toggle between hidden and unhidden. I don't want to use Data Grouping because the information in between the named rows is moved around frequently.

The upper row YEAR2019 and YEAR2020 are to remain visible at all times.

I'm currently trying this but receive errors and can't seem to get it to work -

Sub HideRowsYEAR2020()
    Range(.Cells(.Range("YEAR2019").Row + 1), _
                .Cells(.Range("YEAR2020").Row - 1)).Select
    If Selection.EntireRow.Hidden Then
        Selection.EntireRow.Hidden = False
    Else
        Selection.EntireRow.Hidden = True
    End If
End Sub

Scott Craner Scott Craner · Accepted Answer · 2020-09-18T15:40:43

When you use the . it should be in inside a with statement. Like With ActiveSheet

Also Cells() expects a column reference as the second criterion.

Sub HideRowsYEAR2020()
    With ActiveSheet 'Should change to actual sheet
         With .Range(.Cells(.Range("YEAR2019").Row + 1, 1), .Cells(.Range("YEAR2020").Row - 1, 1))
            If .EntireRow.Hidden Then
                .EntireRow.Hidden = False
            Else
                .EntireRow.Hidden = True
            End If
        End With
    End With
End Sub

Select and Hide Entire Rows In-Between 2 Named Rows

1 Answers