Hide or unhide rows based on value across multiple sheets

[Casper99]

Hi. I am trying to hide/unhide rows across multiple sheets based on the value of 1 or 0 in column A. There are 3 out of 5 sheets that I would like the macro to update automatically. The 1 and 0 are based off of a IF statement tied to Yes/No Drop-down. Yes = 1, No = 0.

If yes is selected, I want the rows to unhide. If no is selected I wanted the rows to hide. It should update the 3 sheets at the same time, so you have to click on each.

Here is the code I have but I get an error when I protect the sheets.


Sub handunhrows()
Dim arrayshts()
Dim lr As Long
Dim i As Long
arrayshts = Array("Milestones", "Summary", "Worksheet")
For Each wsheet In ThisWorkbook.Worksheets
F = Filter(arrayshts, wsheet.Name)
If UBound(F) >= 0 Then
wsheet.Activate
For i = 1 To 10000
If wsheet.Range("A" & i).Value = "0" Then
wsheet.Rows(i & ":" & i).EntireRow.Hidden = True 'this row has an is highlighted by debug when sheet is protected
ElseIf wsheet.Range("A" & i).Value = "1" Then
wsheet.Rows(i & ":" & i).EntireRow.Hidden = False
End If
Next i
End If
Next wsheet
End Sub

[spitfireblue]

Hi Casper,

In future please use code tags around any code that you post.

You will need to unprotect each sheet when you activate it and then protect it again before you activate the next one.

Please Login or Register  to view this content.

[LeoTaxi]

You have to unprotect the sheet in code, just before the For i
after next i you protect the sheet


Kind regards
Leo

[Casper99]

Thank you! It worked!