Hide and unhide rows based on values

Satheesh9012

New Member
Joined
Mar 19, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hi

I need help for one of the codes which is working fine in one form in an excel sheet but not in others, i am trying to hide/unhide certain rows in the sheet based on Yes or no values, if it is Yes a row should be hidden if it is no a row should be hidden so it is working fine in the first form in the excel but when i tried to replicate it in the second form in the same excel sheet in a different row it does not work nor throws an error,

below code i used, i have the range and row changing in 5 other different places in the same sheet

If Range("C32").Value = "Yes" Then
Rows("34:34").EntireRow.Hidden = True
ElseIf Range("C32").Value = "No" Then
Rows("33:33").EntireRow.Hidden = True
Rows("34:34").EntireRow.Hidden = False
End If
 
Try this in the sheet module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Cells(13, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(14).Hidden = False
            Rows(15).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(14).Hidden = True
            Rows(15).Hidden = False
        Else
            Rows("14:15").Hidden = False
        End If
    End If
    
    If Not Intersect(Target, Cells(32, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(33).Hidden = False
            Rows(34).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(33).Hidden = True
            Rows(34).Hidden = False
        Else
            Rows("33:34").Hidden = False
        End If
    End If
    
    If Not Intersect(Target, Cells(51, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(52).Hidden = False
            Rows(53).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(52).Hidden = True
            Rows(53).Hidden = False
        Else
            Rows("52:53").Hidden = False
        End If
    End If
    
    If Not Intersect(Target, Cells(70, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(71).Hidden = False
            Rows(72).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(71).Hidden = True
            Rows(72).Hidden = False
        Else
            Rows("71:72").Hidden = False
        End If
    End If
    
    If Not Intersect(Target, Cells(89, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(90).Hidden = False
            Rows(91).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(90).Hidden = True
            Rows(91).Hidden = False
        Else
            Rows("90:91").Hidden = False
        End If
    End If
    
    If Not Intersect(Target, Cells(108, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(109).Hidden = False
            Rows(110).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(109).Hidden = True
            Rows(110).Hidden = False
        Else
            Rows("109:110").Hidden = False
        End If
    End If

End Sub
 
Upvote 0
Solution

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try this in the sheet module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Cells(13, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(14).Hidden = False
            Rows(15).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(14).Hidden = True
            Rows(15).Hidden = False
        Else
            Rows("14:15").Hidden = False
        End If
    End If
   
    If Not Intersect(Target, Cells(32, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(33).Hidden = False
            Rows(34).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(33).Hidden = True
            Rows(34).Hidden = False
        Else
            Rows("33:34").Hidden = False
        End If
    End If
   
    If Not Intersect(Target, Cells(51, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(52).Hidden = False
            Rows(53).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(52).Hidden = True
            Rows(53).Hidden = False
        Else
            Rows("52:53").Hidden = False
        End If
    End If
   
    If Not Intersect(Target, Cells(70, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(71).Hidden = False
            Rows(72).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(71).Hidden = True
            Rows(72).Hidden = False
        Else
            Rows("71:72").Hidden = False
        End If
    End If
   
    If Not Intersect(Target, Cells(89, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(90).Hidden = False
            Rows(91).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(90).Hidden = True
            Rows(91).Hidden = False
        Else
            Rows("90:91").Hidden = False
        End If
    End If
   
    If Not Intersect(Target, Cells(108, "C")) Is Nothing Then
        If Target.Value = "Yes" Then
            Rows(109).Hidden = False
            Rows(110).Hidden = True
        ElseIf Target.Value = "No" Then
            Rows(109).Hidden = True
            Rows(110).Hidden = False
        Else
            Rows("109:110").Hidden = False
        End If
    End If

End Sub
this worked thanks a ton :)
 
Upvote 0
Here is a more compact version of kanadaaa's code...
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 3 And (Target.Row - 13) Mod 19 = 0 Then
    If LCase(Target.Value) = "yes" Or LCase(Target.Value) = "no" Then
      Target.Offset(1) = LCase(Target.Value) = "no"
      Target.Offset(2) = LCase(Target.Value) = "yes"
    Else
      Target.Offset(1).Resize(2) = ""
    End If
  End If
End Sub
Edit Note: I modified the code slightly about 4 minutes after posting it.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top