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

 

[Fluff]

In what way isn't it working?

 

[fhzhkunming]

In what way isn't it working?

Hi Fluff,

The VBA wasn't triggered when the cell value in D28 or D29 changed because the rows didn't hide.

Thanks

 

[GWteB]

@fhzhkunming, in your code any change of cell D29 isn't captured. How about:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("D28:D29")) Is Nothing Then Exit Sub

    If Range("D28").Value = "Yes" Then
        Rows("29:31").Hidden = True

    ElseIf Range("D28").Value = "No" And Range("D29").Value = "Number" Then
        Rows("31").Hidden = True

    Else
        Rows("29:31").Hidden = False

    End If

End Sub

​

 

[Fluff]

How are those cells being changed?

 

[fhzhkunming]

Each cell is changed from a separate dropdown list on another sheet.

 

[Fluff]

How is it changed? via formula, macro or something else?

 

[fhzhkunming]

@GWteB, thanks for your code. I tried but no lucky. The rows were not hided/unhided.

Thanks anyway.

Frank

 

[GWteB]

The cause of that might be a premature interruption of your own code. Run the code below once and try again. Be sure to paste the code of my post #4 in de module of the sheet to be affected.

Sub RunOnce()
    Application.EnableEvents = True
End Sub

 

[fhzhkunming]

How is it changed? via formula, macro or something else?

The cells are changed by manually select value from dropdown list. The dropdown list for D28 from previous sheet has three options, "Yes", "No", and "Choose answer". The dropdown list for D29 from previous sheet also has three options, "Number", "Budget", and "Choose answer". Choose "Yes" or "No" for each cell will get different calculations for other cells via if statements, so as to impact the display of Rows 29:31.

Thanks