VBA code to unhide row fails

[cterjesen]

I have the following code in a worksheet:

Private Sub Worksheet_Calculate()
Module1.UnProtectIt
Rows("4").Select
Selection.EntireRow.Hidden = False
Dim r As Range
For Each r In Range("AS4:BP4")
r.EntireColumn.Hidden = (r.Value = "No")
Next
Rows("4").Select
Selection.EntireRow.Hidden = True
Module1.ProtectIt
End Sub

The code is intended to unprotect the worksheet, unhide row 4, evaluate a formula in cells AS4:BP4 for either a "Show" or "No" value returned, and then hide columns based on those that return "No". The code should then re-hide row 4 and re-protect the worksheet.

When I initiate the code, it fails at "Rows("4").Select". If I try to run the code without unhiding this row, it fails at "r.EntireColumn.Hidden = (r.Value = "No")".

Any ideas?

 

[jbeaucaire]

Try this change in your syntax:

Rows(4).EntireRow.Hidden = False

You don't need to "select" row 4 to unhide it, and I don't think you need quotes around the 4 in either case.

 

[VoG]

Try

Private Sub Worksheet_Calculate()
Dim r As Range
Module1.UnProtectIt
Rows(4).Hidden = False
For Each r In Range("AS4:BP4")
    r.EntireColumn.Hidden = (r.Value = "No")
Next
Rows(4).Hidden = True
Module1.ProtectIt
End Sub

 

[cterjesen]

Thanks. I changed that and I still get an error message that reads:

"Select method of Range class failed"

When I debug, the Rows(4).Select line is highlighted.

 

[Ragnar1211]

You dont need to select the row. Look at Vog's solution.

All you need is rows(4).Hidden = true

 

[cterjesen]

Thanks again for the quick replies. The code still fails on me. I copied the code from this site. I think it has to do with the protection of the sheet. If I leave the sheet unprotected, the code works.

 

[schielrn]

Can you post the code that is in the procedure UnProtectIt?

 

[cterjesen]

Here is the code for ProtectIt and UnProtectIt.

Sub ProtectIt()
ActiveSheet.Protect Password:="troon", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sub UnProtectIt()
ActiveSheet.Unprotect Password:="troon"
End Sub

Another thought, the action that initiates the change is in a separate worksheet. A drop-down/validation list is selected by the user, which changes the value in the cells in line 4. Could that have anything to do with it?

 

[doofusboy]

action that initiates the change is in a separate worksheet

Most likely your 'UnProtectIt" is unprotecting the Active sheet, not the sheet where you are trying to unhide the row.

 

[cterjesen]

That appears to have been the problem! I will put code in each sheet to specifically unprotect that sheet.

Thanks (everyone) for making me look smarter than I really am......