Code to hide a row or make 0 height based on drop down cell

[jrisebo]

Anyway to hide a row based on a cell value? I have a cell with "Yes" "no" value, if "No", I want it to hide the row below
Thanks

 

[Joe4]

You can use VBA to do that.
You can either have code that you run manually to do it, or you can have VBA code that runs automatically when some cell is manually updated.

If you are interested in either solution, please provide more details (i.e. exactly where you will be entering these "Yes" and "No" values).

 

[jrisebo]

You can use VBA to do that.
You can either have code that you run manually to do it, or you can have VBA code that runs automatically when some cell is manually updated.

If you are interested in either solution, please provide more details (i.e. exactly where you will be entering these "Yes" and "No" values).

Thanks. So Cell C17 will be a drop down, yes or no. I want row 18 to be hidden if answer is no.
Thanks

 

[bebo021999]

Are there other cells with "yes/no" in dropdown, like C17? What are they?

 

[bebo021999]

This for cell C17:
Place in worksheet module (Right click on sheet name/ View code than paste below code into)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Intersect(Target, Range("C17")) Is Nothing Then Exit Sub
With Target.Offset(1, 0).EntireRow
    .Hidden = False
    If Target.Value = "Yes" Then
        .Hidden = False
    Else
        .Hidden = True
    End If
End With
Range("C17").Select
Application.ScreenUpdating = True
End Sub

 

[jrisebo]

This for cell C17:
Place in worksheet module (Right click on sheet name/ View code than paste below code into)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Intersect(Target, Range("C17")) Is Nothing Then Exit Sub
With Target.Offset(1, 0).EntireRow
    .Hidden = False
    If Target.Value = "Yes" Then
        .Hidden = False
    Else
        .Hidden = True
    End If
End With
Range("C17").Select
Application.ScreenUpdating = True
End Sub

When I choose "no" it hides row 18, but when switch back to yes, it does not unhide.

 

[Joe4]

Try these tweaks to bebo0219999's original code, which works for any case (i.e. "YES", "Yes", "yes"), and accounts for the possibility of multiple cells being updated at once.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C17")) Is Nothing Then Exit Sub

    With Range("C17").Offset(1, 0).EntireRow
        .Hidden = False
        If LCase(Range("C17").Value) = "yes" Then
            .Hidden = False
        Else
            .Hidden = True
        End If
    End With

End Sub

 

[jrisebo]

Thanks that works.

 

[Joe4]

You are welcome.
Glad we could help!