Loop through worksheets to unhide rows

[Punsterr]

Hi all,

I'm trying to write a sub where the user will click a box ("Hide Unused
Rows in all Worksheets") and the macro will go through each worksheet
in my workbook and will unhide all of the hidden rows. Both the
workbook and each individual worksheet have protection - we'll assume
both passwords are "password" for this example.

Issues:

1) The macro below runs, but it is not unhiding the rows.
2) The way I've written it will check 200 rows in each worksheet. Is
there an easy way for it to check only through the last active row on
each worksheet?

Sub UnhideAllRowsWorkbook()

' Unhides all rows throughout the workbook

Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="Password"
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Unprotect Password:="Password"
Rows("1:200").EntireRow.Hidden = False
WS.Protect Password:="Password",
userinterfaceonly:=True, AllowFormattingCells:=True,
AllowFormattingColumns:=True, AllowFormattingRows:=True
WS.EnableSelection = xlNoRestrictions
Next WS
ActiveWorkbook.Protect Password:="Password", Structure:=True,
Windows:=True
Cells(1, 1).Select
Application.ScreenUpdating = True
MsgBox "All rows throughout the workbook are now visible.",
vbOKOnly
End Sub

 

[Tom Ogilvy]

Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="Password"
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Unprotect Password:="Password"
' change made:
ws.UserRange.EntireRow.Hidden = False
WS.Protect Password:="Password",
userinterfaceonly:=True, AllowFormattingCells:=True,
AllowFormattingColumns:=True, AllowFormattingRows:=True
WS.EnableSelection = xlNoRestrictions
Next WS
ActiveWorkbook.Protect Password:="Password", Structure:=True,
Windows:=True
Cells(1, 1).Select
Application.ScreenUpdating = True
MsgBox "All rows throughout the workbook are now visible.",
vbOKOnly
End Sub

 

[Punsterr]

Tom,

Thanks for your quick response. However, there appears to be an error
with the change you suggested (ws.UserRange.EntireRow.Hidden = False).
I get a compile error "method or data member not found."

I also am still curious as to why my initial attempt didn't work (just
unhiding all of rows 1-200 on each worksheet). The macro runs, it
doesn't error out, but yet it doesn't unhide the rows. Is there an
issue with it being called from another sub?

 

[Tom Ogilvy]

ws.UserRange.EntireRow.Hidden = False
was a typo

it should be

ws.UsedRange.EntireRow.Hidden = False

it didn't work for the same reason as your next post. The unqualified rows
refered to the activesheet.

 

[Punsterr]

Got it. Thanks a bunch!