Use VBA to hide/unhide rows based on cell values

[fhzhkunming]

Hi Folks,

I have a simple situation but I couldn't make it working.
I want to hide Rows 29-31 if D28 = "Yes" (Fig.1), and want to hide only Row 31 if D28 ="No" and D29 = "Number" (Fig. 2). In other words, if D28 = "Yes", Rows 29-31 are empty (Actually contents in these rows were invisible by conditional formatting); if D28 = "No" and D29 = "Number", only Row 31 is empty row (contents invisible). BTW, values of D28 and D29 were automatically copied from another sheet. I want this hide/unhide event to be automatically triggered.

Below is my code. Could anyone please help me out?

Thanks in advance.

Frank

Private Sub Worksheet_Change(ByVal Target As Range)

'MsgBox "Changed: " & Target.Address

    Application.EnableEvents = False 'pervent triggering another change event

If Not Intersect(Target, Range("D28")) Is Nothing Then
    If Range("D28").Value = "Yes" Then
                Range("D29:D31").EntireRow.Hidden = True
            ElseIf Range("D28").Value = "No" And Range("D29").Value = "Number" Then
                Range("D31").EntireRow.Hidden = True
            Else
                Range("D29:D31").EntireRow.Hidden = False
    
        End If

End If

Application.EnableEvents = True 're-enable events in the end

End Sub

 

[fhzhkunming]

Hi Folks,

I have a new problem with the code to hide/unhide rows based on cell values: It works for most of the steps except for the step "to hide Row33 of sheet 'Stage C-Step 11'" when G26="No" and G30 = "Number" as shown with the comments in the line.
Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G26")) Is Nothing Then
    If Range("G26").Value = "Yes" Then
       Range("G30").Value = "Choose answer"
       Range("G31:G32").ClearContents
       Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = True
        
       ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
            Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True                   'This step is not working.
       Else
            Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = False
    End If

End If
                
If Not Intersect(Target, Range("G30")) Is Nothing Then   
     Range("G31:G32").ClearContents
End If
        
End Sub

I don't know what I did wrong. Could anyone please help me out?

Thanks

Frank

 

[Fluff]

In what way isn't it working?

 

[fhzhkunming]

When conditions are met, i.e., when the G26=No and G30= Number, Row33 on sheet "Stage C-Step 11" was not hidden.

 

[Fluff]

In that case I suspect that G26 & G30 do not exactly equal No & Number.
As VBA is case sensitive if either of the cells have a lower case N then it won't work.

 

[fhzhkunming]

In that case I suspect that G26 & G30 do not exactly equal No & Number.
As VBA is case sensitive if either of the cells have a lower case N then it won't work.

I double checked, but the values in G26 and G30 are "No" and "Number", respectively. It seems some problem with "ElseIF ...Then" statement. Anything in between are not working. Did I do something wrong with this statement?
Thanks

 

[Fluff]

Are you changing G26 after you have put Number in G30?

 

[fhzhkunming]

Are you changing G26 after you have put Number in G30?

Nope. G26 has 3 options, "Yes", "No", and "Choose answer", which can be chosen one of them from drop-down list. G30 also has 3 options, "Number", "Budget", and "Choose answer", and only one should be chosen from drop-down list. If G26 = Yes, no further action is need for G30. If G26 = No, one of the options will be chosen for G30; so if G26=No and G30=Number, then Row 33 on Sheet "Stage C-Step 11" should be hidden, but it doesn't work.

Thanks

 

[Fluff]

In that case how about

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G26")) Is Nothing Then
    If Range("G26").Value = "Yes" Then
       Range("G30").Value = "Choose answer"
       Range("G31:G32").ClearContents
       Rows("31:33").EntireRow.Hidden = True

       ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Rows("33").EntireRow.Hidden = True                   'This step is not working.
       Else
           Rows("31:33").EntireRow.Hidden = False
    End If

End If

If Not Intersect(Target, Range("G30")) Is Nothing Then
     If Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Rows("33").EntireRow.Hidden = True
     End If
     Range("G31:G32").ClearContents
End If

End Sub

Your code was only hiding rows based on G26 changing.

 

[fhzhkunming]

In that case how about

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G26")) Is Nothing Then
    If Range("G26").Value = "Yes" Then
       Range("G30").Value = "Choose answer"
       Range("G31:G32").ClearContents
       Rows("31:33").EntireRow.Hidden = True

       ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Rows("33").EntireRow.Hidden = True                   'This step is not working.
       Else
           Rows("31:33").EntireRow.Hidden = False
    End If

End If

If Not Intersect(Target, Range("G30")) Is Nothing Then
     If Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Rows("33").EntireRow.Hidden = True
     End If
     Range("G31:G32").ClearContents
End If

End Sub

Your code was only hiding rows based on G26 changing.

Thanks for the code. However, it still doesn't work, and even other parts of the code, such as to hide Rows(31:33) when G26 = Yes, doesn't work now.
What I want is the action to hide Row33 is only activated when G26 = No and G30 = Number.

 

[Fluff]

It works fine for me & there is nothing in the change I made that would affect changing G26 to Yes.