Hide next row based on drop down calue (yes or no)

[slk1987]

Hi there,

Hoping someone can help me with the following:

I have a yes or no dropdown in cell J13 and would like to hide row 14 if the value in j13 is no.

If the value in j13 is yes, I would like row 14 to unhide.

I would like to run the code each time the value in J13 is changed.

Any assistance would be greatly appreciated.

 

[bebo021999]

Right click on tab'name, View Code, then paste below code into

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "J13" Then Exit Sub
With Rows(14)
    Select Case Target.Value
        Case "Yes"
            .Hidden = True
        Case "No"
            .Hidden = False
    End Select
End With
End Sub

This code will trigger any change in cell J13

 

[slk1987]

Right click on tab'name, View Code, then paste below code into

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "J13" Then Exit Sub
With Rows(14)
    Select Case Target.Value
        Case "Yes"
            .Hidden = True
        Case "No"
            .Hidden = False
    End Select
End With
End Sub

This code will trigger any change in cell J13

Great, thank you for posting. I seem to be running into an issue posting it with the already existing VBA:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&
If Intersect(Target, Range("C6:C7")) Is Nothing Then Exit Sub ' if C4 or C5 does not change then do nothing
With Sheets("Income Map") ' reference to A2:A72 of Income Map
.Rows("7:72").Hidden = False ' first, unhide all rows
For i = 7 To 72
If .Cells(i, 1).Value = 0 Then .Rows(i).Hidden = True ' hide rows those = 0
Next
End With
End Sub


How would I include both sections of code?

Thanks again!

 

[zero269]

How would I include both sections of code?

1. If these two are to be treated separately - which you should do for testing the new code - then paste it below any End Sub.
2. If you are replacing your old code with the new code, then just Comment Block the entire old code block while you test the new code block.
3. If you are trying to combine the two - I would have shared that code up front so people could advise based on that. Looks like you may need to wait for bebo to get back to you.

When sharing or posting code, it's recommended you use the Quick-wrap selection as VBA code on the toolbar.

... like so:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&
If Intersect(Target, Range("C6:C7")) Is Nothing Then Exit Sub ' if C4 or C5 does not change then do nothing
With Sheets("Income Map") ' reference to A2:A72 of Income Map
.Rows("7:72").Hidden = False ' first, unhide all rows
For i = 7 To 72
If .Cells(i, 1).Value = 0 Then .Rows(i).Hidden = True ' hide rows those = 0
Next
End With
End Sub

I left out your Option Explicit because that's not part of the code block... but important to share that fact when asking for assistance.

When sharing directly for your Workbook, use this add-in if you are not already aware of it...

Excel Add-ins

www.mrexcel.com

Best regards,

 

[slk1987]

Thank you for the feedback @zero269, here is what I have:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&
If Intersect(Target, Range("C6:C7")) Is Nothing Then Exit Sub ' if C4 or C5 does not change then do nothing
With Sheets("Income Map") ' reference to A2:A72 of Income Map
    .Rows("7:72").Hidden = False ' first, unhide all rows
    For i = 7 To 72
        If .Cells(i, 1).Value = 0 Then .Rows(i).Hidden = True ' hide rows those = 0
    Next
End With
End Sub

Private Sub Hide_Row(ByVal Target As Range)
If Target.Address(0, 0) <> "J13" Then Exit Sub
With Rows(14)
    Select Case Target.Value
        Case "No"
            .Hidden = True
        Case "Yes"
            .Hidden = False
    End Select
End With
End Sub

The worksheet_change sub is working but looks like an issue with the row hiding based on the value in J13. At this point, there is no change regardless of the value in J13.

Thanks!

 

[slk1987]

Hi there, just wanted to follow up here and see if anyone has thoughts on the discussion above. Appreciate any help.