10101
Reputation: 2412
Unhide rows one by one
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.
Unhide rows:
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
Hide all:
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
Upvotes: 0
Views: 107
Answers (1)
Tin Bum
Reputation: 1491
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
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
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
=====================================
As per comments
I had the following line wrong originally
ThisWorkbook.Sheets("Filling form").Rows(ThisRng).EntireRow.Hidden = False
Upvotes: 1
Related Questions
- Unhide rows within a range one by one using a command button
- Excel VBA Hiding Rows
- Unhide rows in logical order
- Unhide rows based on cell value
- Unhiding hidden rows in Excel
- Excel Autohide/Unhide rows based on Multiple Cells
- Excel Hiding Rows with VBA
- Hide Rows in excel VBA
- Unhide Excel Rows Based on Cell Value
- Unhide rows in Excel with VBA