Hiding Rows

[rosaW]

Hello,

I am trying to hide a different row on two different sheets (sheet 1 and sheet 2) whenever the value of a certain cell in sheet 1 is null.

So when cell "O$2" on sheet1 is null, i want cells ("$A$34:$I$34") to be hidden on sheet1 and cells ("$D$5:$H$5") to be hidden on sheet2.

What VBA code would i need to do this?

 

[doofusboy]

You could probably put something like this in Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Len(Range("O$2")) = 0 Then
        Sheet1.Range("$A$34:$I$34").EntireRow.Hidden = True
        Sheet2.Range("$D$5:$H$5").EntireRow.Hidden = True
    End If
End Sub

 

[rosaW]

The rows still are not hidden. Am i missing something?

Currently when the value in $O$2 is null i get "#N/A" which is not a problem other than that i don't want it showing, in order that the end user does not get confused.

 

[doofusboy]

Try wrapping whatever [formula?] is giving you the "#N/A" with the ISERROR() function and an If statement so that it returns you a blank cell instead of "#N/A".

If it's a formula that is giving you "#N/A", try something like:

=IF(ISERROR(your_formula),"",your_formula) ...and then employ the other code I posted.

 

[rosaW]

Thank you, that partially worked.
But this i what i have found to work perfect for one sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("$O$2") = "" Then
Range("$A$34:$I$34").EntireRow.Hidden = True
Else
Range("$A$34:$I$34").EntireRow.Hidden = False
End If

End Sub

This code only applies for my current sheet (sheet1) i need sheet2 to also hide the row ("$D$5:$H$5").

How would i be able to include sheet2 in the code so that the rows on both sheets are deleted at the same time when the condition of ("$O$2") = "" ?

 

[doofusboy]

Have you tried something like the following?

[B]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("$O$2") = "" Then[/B]
[B]Range("$A$34:$I$34").EntireRow.Hidden = True[/B]
[B][COLOR=red]Sheet2.Range("$D$5:$H$5").EntireRow.Hidden = True[/COLOR][/B]
[B]Else[/B]
[B]Range("$A$34:$I$34").EntireRow.Hidden = False[/B]
[B]End If[/B]

[B]End Sub[/B]