Hide and unhide rows based on values

Satheesh9012

New Member
Joined
Mar 19, 2021
Messages
22
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
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
344
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Satheesh9012

New Member
Joined
Mar 19, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,588
Messages
5,637,275
Members
416,963
Latest member
samfuge

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
Top