取消隐藏行一一 [英] Unhide rows one by one

查看：39
本文介绍了取消隐藏行一一的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有VBA可以按一定顺序逐行取消隐藏行.因此，通过单击按钮，行将按一定顺序逐个被隐藏.当前解决方案的问题是它太长和复杂.我想使代码更短，更有效.范围是从174 -1开始并以5行递增，并且递减，因此我认为应该有一些更好的方法来使某些数学函数执行此过程.

I have VBA to unhide rows one by one in certain order. So by clicking button rows are getting unhidden in certain order one by one. The problem with current solution is that it is too long and complex. I would like to make code more shorter and effective. Ranges are in 5 row increments starting from 174 -1 and reducing, so I think there should be some better solution for making some mathematics function doing this procedure.

取消隐藏行:

Sub UnhideJobs()
Static counter As Byte

    counter = (counter + 1) Mod 26

    Select Case counter
                Case 1
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("169:173").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
                Case 2
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("164:168").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
                Case 3
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("159:163").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
                Case 4
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("154:158").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 5
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("149:153").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 6
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("144:148").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 7
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("139:143").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 8
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("134:138").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 9
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("129:133").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 10
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("124:128").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 11
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("119:123").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 12
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("114:118").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 13
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("109:113").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 14
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("104:108").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 15
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("99:103").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 16
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("94:98").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
            Case 17
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("89:93").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
        Case 18
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("84:88").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
        Case 19
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("79:83").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
            Application.ScreenUpdating = True
        Case 20
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("74:78").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
        Case 21
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("69:73").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
            Application.ScreenUpdating = True
        Case 22
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("64:68").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
            Application.ScreenUpdating = True
        Case 23
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("59:63").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
            Application.ScreenUpdating = True
        Case 24
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("54:58").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
            Application.ScreenUpdating = True
        Case 25
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("49:53").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
            Application.ScreenUpdating = True
        Case 26
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("49:53").EntireRow.Hidden = False
            ThisWorkbook.Sheets("Filling form").Protect
            Application.ScreenUpdating = True
    End Select
End Sub

全部隐藏:

Sub HideAllJobs()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Filling form").Unprotect
Rows("49:173").EntireRow.Hidden = True
ThisWorkbook.Sheets("Filling form").Protect
Application.ScreenUpdating = True
End Sub

推荐答案

在HideAllJobs中将Counter重置为0，并使用Public变量代替静态行

Reset your Counter to 0 in HideAllJobs and use a Public variable in place of the static line

Public counter As Integer

Sub HideAllJobs()
   'Static counter As Byte
   Application.ScreenUpdating = False
   ThisWorkbook.Sheets("Filling form").Unprotect
   Rows("49:173").EntireRow.Hidden = True
   counter = 0
   ThisWorkbook.Sheets("Filling form").Protect
   Application.ScreenUpdating = True
End Sub

Sub UnhideJobs()
    'Static counter As Byte
    counter = (counter + 1) Mod 26
    Select Case counter
      Case 1
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("169:173").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 2
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("164:168").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 3
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("159:163").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 4
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("154:158").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 5
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("149:153").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 6
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("144:148").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 7
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("139:143").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 8
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("134:138").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 9
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("129:133").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 10
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("124:128").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 11
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("119:123").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 12
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("114:118").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 13
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("109:113").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 14
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("104:108").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 15
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("99:103").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 16
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("94:98").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 17
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("89:93").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 18
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("84:88").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 19
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("79:83").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 20
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("74:78").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 21
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("69:73").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 22
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("64:68").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 23
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("59:63").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 24
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("54:58").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 25
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("49:53").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True

      Case 26
        Application.ScreenUpdating = False
        ThisWorkbook.Sheets("Filling form").Unprotect
            Rows("49:53").EntireRow.Hidden = False
        ThisWorkbook.Sheets("Filling form").Protect
        Application.ScreenUpdating = True
    End Select
End Sub

附录-新的精简Unhide例程

Addendum - New condensed UnHide Routine

Sub NewUnhideJobs()
    Dim RngTxt As String, RngAR() As String, ThisRng As String

    counter = (counter + 1) Mod 26
    counter = IIf(counter = 0, 1, counter)

    RngTxt = "169:173_164:168_159:163_154:158_149:153_144:148_139:143_134:138_129:133"
    RngTxt = RngTxt & "_124:128_119:123_114:118_109:113_104:108_99:103_94:98"
    RngTxt = RngTxt & "_89:93_84:88_79:83_74:78_69:73_64:68_59:63_54:58_49:53_49:53"

    RngAR = Split(RngTxt, "_")
    ThisRng = RngAR(counter - 1)

    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Filling form").Unprotect
    ThisWorkbook.Sheets("Filling form").Rows(ThisRng).EntireRow.Hidden = False
    ThisWorkbook.Sheets("Filling form").Protect
    Application.ScreenUpdating = True
End Sub

您还可以使用一种更简单的方法来进一步减少代码-您的范围是从174 -1开始以5行为增量，然后减小，因此

You could also use a simpler method and reduce code further - your ranges are in 5 row increments starting from 174 -1 and reducing, so

ThisRng = "" & (174 - (counter * 5)) & ":" & (174 - (counter * 5) + 4)

可以工作，并且省去几行

would work and eliminate a few more lines

===================================

=====================================

根据评论

我本来以下一行是错误的

I had the following line wrong originally

ThisWorkbook.Sheets("Filling form").Rows(ThisRng).EntireRow.Hidden = False

这篇关于取消隐藏行一一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆