Hide & unhide rows

[timesareGMT]

Hi all,

I create a calculation page on which the final result displays in cell D21.

How can I set up a macro to:
1. If D21 < 0.5, hide rows 22 to 28.
2. If D21 > 0.5, unhide rows 22 to 28.

And, how can I prevent the assigned macro from being stopped running when I password protect the spreadsheet?

Thanks a lot.

 

[dangre]

What happens if the value is exactly 0.5?

Here is some VBA that should do the trick, assuming the workbook protection is not password protected, and that rows should be displayed if value is exactly 0.5...

Sub hiderows()
ActiveWorkbook.Unprotect
Range("D22:D28").Select
If Range("D21").Value < 0.5 Then
    Selection.EntireRow.Hidden = True
Else
    Selection.EntireRow.Hidden = False
End If
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

 

[timesareGMT]

What happens if the value is exactly 0.5?

Here is some VBA that should do the trick, assuming the workbook protection is not password protected, and that rows should be displayed if value is exactly 0.5...

Sub hiderows()
ActiveWorkbook.Unprotect
Range("D22:D28").Select
If Range("D21").Value < 0.5 Then
    Selection.EntireRow.Hidden = True
Else
    Selection.EntireRow.Hidden = False
End If
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Thanks for the info.

But how can I make it run automatically without manually pressing ALT+F8?

And again, how to get it work under the password protection condition?

Thanks.

 

[dangre]

Thanks for the info.

But how can I make it run automatically without manually pressing ALT+F8?

And again, how to get it work under the password protection condition?

Thanks.

To run automatically depends on the trigger. Do you want it to run when the sheet is activated or when the value in D21 is changed? Is D21 a reference to another cell, or is the value of D21 entered/updated directly in the cell?

 

[timesareGMT]

To run automatically depends on the trigger. Do you want it to run when the sheet is activated or when the value in D21 is changed? Is D21 a reference to another cell, or is the value of D21 entered/updated directly in the cell?

Thanks for your reply.

I want the macro run automatically once D21 has been changed.

The value of D21 is an outcome of sequences of calculations being presented in terms of a numerical value (e.g. 0.123)

Thanks.

 

[dangre]

Thanks for your reply.

I want the macro run automatically once D21 has been changed.

The value of D21 is an outcome of sequences of calculations being presented in terms of a numerical value (e.g. 0.123)

Thanks.

This code should be placed in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$21" Then
    Range("D22:D28").Select
    If Range("D21").Value < 0.5 Then
        Selection.EntireRow.Hidden = True
    Else
        Selection.EntireRow.Hidden = False
    End If
End If
End Sub

Are you password protecting the individual worksheet, or the entire workbook?

 

[Weaver]

right click the tab, choose 'view code' and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo safeExit
    If Target.Address = "$D$21" Then
        Application.EnableEvents = False
        Range("D22:D28").EntireRow.Hidden = (Target.Value > 0.5)
    End If
safeExit:
    Application.EnableEvents = True
End Sub

This should hide/unhide the rows when the value changes

 

[Weaver]

This code should be placed in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$21" Then
    Range("D22:D28").Select
    If Range("D21").Value < 0.5 Then
        Selection.EntireRow.Hidden = True
    Else
        Selection.EntireRow.Hidden = False
    End If
End If
End Sub

Are you password protecting the individual worksheet, or the entire workbook?

Dangre, you're going to have problems with this version as hiding/unhiding the rows counts as a worksheet change, so the code will end up calling itself into an infinite loop

 

[dangre]

Dangre, you're going to have problems with this version as hiding/unhiding the rows counts as a worksheet change, so the code will end up calling itself into an infinite loop

The code does not execute unless the change is made to cell D21 explicitly.

 

[timesareGMT]

right click the tab, choose 'view code' and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo safeExit
    If Target.Address = "$D$21" Then
        Application.EnableEvents = False
        Range("D22:D28").EntireRow.Hidden = (Target.Value > 0.5)
    End If
safeExit:
    Application.EnableEvents = True
End Sub

This should hide/unhide the rows when the value changes

Thanks for your info.

But have I missed something? The automatic hiding/unhiding function was not working.

Your further guidance is sought.

Thanks.