admin 管理员组

文章数量: 1086019

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
Share Improve this question edited Mar 27 at 21:49 Tim Williams 167k8 gold badges100 silver badges141 bronze badges asked Mar 27 at 21:46 user30085781user30085781 111 bronze badge 1
  • 1 You could use named ranges "block1" , "block2" etc instead of row numbers. – Tim Williams Commented Mar 27 at 21:50
Add a comment  | 

1 Answer 1

Reset to default 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

本文标签：

Error[2]: Invalid argument supplied for foreach(), File: /www/wwwroot/roclinux.cn/tmp/view_template_quzhiwa_htm_read.htm, Line: 58
File: /www/wwwroot/roclinux.cn/tmp/route_read.php, Line: 205, include(/www/wwwroot/roclinux.cn/tmp/view_template_quzhiwa_htm_read.htm)
File: /www/wwwroot/roclinux.cn/tmp/index.inc.php, Line: 129, include(/www/wwwroot/roclinux.cn/tmp/route_read.php)
File: /www/wwwroot/roclinux.cn/index.php, Line: 29, include(/www/wwwroot/roclinux.cn/tmp/index.inc.php)

版权声明：本文标题：excel - in one column, several groups of hidden rows, insert new row and they all move down, how to stop? - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1744066671a2527727.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。