CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2402

Unhide rows in logical order

I have code for Hiding and Unhiding rows in Excel sheet. With help of user1302114 (Unhide rows one by one) code was shorten and now it is functioning much more quicker.

Current problem is:

  1. Running twice NewUnhideJobs (position of second section)
  2. Running once HideAllJobs
  3. Running NewUnhideJobs

As the result we have position of third section and not first as it should be. So NewUnhideJobs does not start from 0 but continues to do previous procedure.

Removing of Static counter As Byte was tried but code does not work without it.

counter = 0 was also tried in HideAllJobs without success.

Hiding rows:

Sub NewUnhideJobs()
Static counter As Byte
    Dim RngTxt As String, RngAR() As String, ThisRng As String

    counter = (counter + 1) Mod 26

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

    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

Unhiding rows:

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

Upvotes: 0

Views: 31

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You must declare your counter as a global variable.

Public counter As Long 'public variable

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

    counter = (counter + 1) Mod 26

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

    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


Sub HideAllJobs()
   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

Otherwise your counter = 0 doesn't have any effect on the counter in NewUnhideJobs because you have declared 2 distinct counter variables, one for each procedure/sub.

Upvotes: 2

Related Questions