Hide rows WITHOUT Macro

[acruthi]

Hi Forum,

I'm trying to hide rows based on the value of a cell but I want to know if there was a way to do it WITHOUT macro.

I read that one way to do it is to Right click sheet tab, view code and paste code there. It did not work for me

I want cells 93 and 94 to remain hidden and only become visible when the value of C3 is "Total"
If the value of C4 is changed from "Total" then rows 93 and 94 should be hidden.

Private Sub Worksheet_Hide()
Rows("93:94").Hidden = True
If Intersect(Target, Range("C4")) Is Nothing Then Exit Sub
If Range("C4").Value = "Total" Then Exit Sub
Rows("93:94").Hidden = False
End Sub

Please help

 

[jbeaucaire]

If the value of C4 is manually controlled, you can use as Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Rows("93:94").Hidden = Range("C4").Value <> "Total"
End Sub

If the value of C4 is controlled by a formula, then you would need to use this:

Private Sub worksheet_Calculate()
    Rows("93:94").Hidden = Range("C4").Value <> "Total"
End Sub

 

[acruthi]

Hi Jbeaucaire

Thanks for replying. I used your code by Right clicking sheet tab, view code and paste code there. Is this the right place to add the code. I have not turned on macro's.

It unfortunately did not hide rows 93:94... any reason why? Here is what I pasted:


Private Sub worksheet_Summary Trends()
Rows("93:94").Hidden = Range("C4").Value <> "Total"
End Sub

Thanks

 

[acruthi]

This finally worked. Thanks all for your help!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("C4").Address And Range("C4").Value = "Total" Then
Range("93:94").Rows.Hidden = False
Else
Range("93:94").Rows.Hidden = True
End If
End Sub

 

[jbeaucaire]

I don't see why the need for all the extra:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C4")) Is Nothing Then
        Rows("93:94").Hidden = Range("C4").Value <> "Total"
    End if 
End Sub