Conditionally Hide/Unhide Rows

[LBinGA]

Hi all:

I have the following code which works beautifully for all the cells in the Range, except I need to add the following condition:

For every Row in Range e19:22 whose Value equals the row above it, Hide the Row. (This should not affect the other rows in the C Column Range list). The rows always begin as hidden. Can anyone help?

Private Sub Worksheet_Calculate()

Dim Cell As Range
On Error GoTo ErrHandler
For Each Cell In Range("e18:e22,C41:C42,C44:C49,C62")

Application.ScreenUpdating = False
Application.EnableEvents = False
ThisWorkbook.Sheets("Select Ren").Unprotect

If Cell.Value > 0 Then
Cell.EntireRow.Hidden = False
Else
Cell.EntireRow.Hidden = True
End If

Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
ThisWorkbook.Sheets("Select Ren").Protect

End Sub

Thanks so much for any input.

LB in GA

 

[lexxie2013]

Try the following (on a copy of your workbook !)

Private Sub Worksheet_Calculate()

 Dim Cell As Range
 On Error GoTo ErrHandler
 For Each Cell In Range("e18:e22")

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 ThisWorkbook.Sheets("Select Ren ").Unprotect

 If Cell.Value > 0 And Cell.Value <> Cell.Offset(-1, 0).Value Then
 Cell.EntireRow.Hidden = False
 Else
 Cell.EntireRow.Hidden = True
 End If

 Next

For Each Cell In Range("C41:C42,C44:C49,C62")

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 ThisWorkbook.Sheets("Select Ren ").Unprotect

 If Cell.Value > 0 Then
 Cell.EntireRow.Hidden = False
 Else
 Cell.EntireRow.Hidden = True
 End If

 Next




ErrHandler:
 Application.ScreenUpdating = True
 Application.EnableEvents = True
 ThisWorkbook.Sheets("Select Ren").Protect

 End Sub

 

[LBinGA]

Thank you for responding! That should have worked but it's not unhiding any of the rows, with or without data, equal or not equal to the row above it. All rows stay hidden.

LB in GA

Try the following (on a copy of your workbook !)

Private Sub Worksheet_Calculate()

 Dim Cell As Range
 On Error GoTo ErrHandler
 For Each Cell In Range("e18:e22")

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 ThisWorkbook.Sheets("Select Ren ").Unprotect

 If Cell.Value > 0 And Cell.Value <> Cell.Offset(-1, 0).Value Then
 Cell.EntireRow.Hidden = False
 Else
 Cell.EntireRow.Hidden = True
 End If

 Next

For Each Cell In Range("C41:C42,C44:C49,C62")
  Application.ScreenUpdating = False
 Application.EnableEvents = False
 ThisWorkbook.Sheets("Select Ren ").Unprotect

 If Cell.Value > 0 Then
 Cell.EntireRow.Hidden = False
 Else
 Cell.EntireRow.Hidden = True
 End If

 Next
ErrHandler:
 Application.ScreenUpdating = True
 Application.EnableEvents = True
 ThisWorkbook.Sheets("Select Ren").Protect

 End Sub

 

[lexxie2013]

I've put an extra space in the sheet name "Select Ren " in two places. Can you see if removing them improves it ?

 

[LBinGA]

THANK YOU!

It works like a champ now. Much obliged!

I had played with taking the e18:e22 out of the list but never quite got it. This did it, so THANKS!

Happy Dance!

LB in GA