I have several categories which I want the BTN_X to hide or unhide rows associated with each category. The code below works, but if i want to add an additional row (say add 217) in the first category, the location shifts all rows below it. How can i make the ROWS(xx:xx).select variable as to not effect the rest of the worksheet? I would like to add a second button used to add new row. Thinking this would evaluate and be an n+1 to all the locations below, but seems overly complicated. thoughts?

'Category 1
Sub BTN_1()
    Rows("200:216").Select

    If Selection.EntireRow.Hidden = True Then
        Selection.EntireRow.Hidden = False
    Else
        Selection.EntireRow.Hidden = True
    End If
End Sub

'Category 2
Sub BTN_2()

    Rows("218:234").Select

    If Selection.EntireRow.Hidden = True Then
        Selection.EntireRow.Hidden = False
    Else
        Selection.EntireRow.Hidden = True
    End If
End Sub
``

insert row and the lower categories' lost row range

I have several categories which I want the BTN_X to hide or unhide rows associated with each category. The code below works, but if i want to add an additional row (say add 217) in the first category, the location shifts all rows below it. How can i make the ROWS(xx:xx).select variable as to not effect the rest of the worksheet? I would like to add a second button used to add new row. Thinking this would evaluate and be an n+1 to all the locations below, but seems overly complicated. thoughts?

'Category 1
Sub BTN_1()
    Rows("200:216").Select

    If Selection.EntireRow.Hidden = True Then
        Selection.EntireRow.Hidden = False
    Else
        Selection.EntireRow.Hidden = True
    End If
End Sub

'Category 2
Sub BTN_2()

    Rows("218:234").Select

    If Selection.EntireRow.Hidden = True Then
        Selection.EntireRow.Hidden = False
    Else
        Selection.EntireRow.Hidden = True
    End If
End Sub
``

insert row and the lower categories' lost row range

• excel
• vba
• button
• insert
• hidden

• 1 You could use named ranges "block1" , "block2" etc instead of row numbers. – Tim Williams Commented Mar 27 at 21:50

1 Answer 1

If you use named ranges instead of row numbers you can do something like this:

Sub BTN_1()
    ToggleRowVis "Block_1"
End Sub

Sub BTN_2()
    ToggleRowVis "Block_2"
End Sub

'Hide show rows for the named range `nm`
Sub ToggleRowVis(nm As String)
    With Range(nm).EntireRow
        .Hidden = Not .Hidden
    End With
End Sub