hiding rows based on cell content (multiple options)

[Blanchetdb]

If presently have the following coding and it works for this specific case but I need to add other options as well

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$37" Then

Dim arCases As Variant
Dim res As Variant

arCases = Array("Term", "Indeterminate", "Transfer", "Student", "Term extension", "As required", "Assignment", "Indéterminé", "Mutation", "Selon le besoin", "Terme", "prolongation du terme", "affectation", "Étudiant(e)")
res = Application.Match(Target, arCases, 0)

If IsError(res) Then
Rows("104:112").Hidden = False
Else
Rows("104:112").Hidden = True
End If
End Sub

I need to add other option such as:

If Target.Address = "$H$04" Then

Dim arCases1 As Variant
Dim res1 As Variant

arCases1 = Array("X")
res1 = Application.Match(Target, arCases1, 0)


If IsError(res1) Then
Rows("36:77").Hidden = True
Else
Rows("36:37").Hidden = False
End If
End Sub

How do I merge them together and enable myself to add even more options as my form progresses?

 

[ask2tsp]

Give this a try

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim arCases As Variant
    Dim res     As Variant
    Dim shRows  As String
    
    Select Case Target.Address
        Case "$C$37"
            arCases = Array("Term", "Indeterminate", "Transfer", "Student", "Term extension", _
            "As required", "Assignment", "Indéterminé", "Mutation", "Selon le besoin", _
            "Terme", "prolongation du terme", "affectation", "Étudiant(e)")
            shRows = "104:112"
            
         Case "$H$04"
            arCases = Array("X")
            shRows = "36:77"
            
        'add more cases  like this...
    End Select
    
    res = Application.Match(Target, arCases, 0)
    
    If IsError(res) Then
        Rows(shRows).Hidden = False
    Else
        Rows(shRows).Hidden = True
    End If
    
End Sub

 

[ask2tsp]

Sorry, fotget the previous reply it is wrong. This one does work

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim arCases As Variant
    Dim res     As Variant
    Dim shRows  As Range
    
    Select Case Target.Address
        Case "$C$37"
            arCases = Array("Term", "Indeterminate", "Transfer", "Student", "Term extension", _
            "As required", "Assignment", "Indéterminé", "Mutation", "Selon le besoin", _
            "Terme", "prolongation du terme", "affectation", "Étudiant(e)")
            Set shRows = Rows("104:112")
            
         Case "$H$4"
            arCases = Array("X")
            Set shRows = Rows("36:77")
            
        'add more cases  like this here ...
        
        Case Else
            Exit Sub
    End Select
        
    res = Application.Match(Target, arCases, 0)
    
    If IsError(res) Then
        shRows.Rows.Hidden = True
    Else
        shRows.Rows.Hidden = False
    End If
    
End Sub

 

[Blanchetdb]

thank you ..... it works but for case "$H$4", I need the rows ("36:77") to be hidden....how do you do that?

 

[ask2tsp]

Well if you change the value of cell C4, the target.address value will be "$C$4" therefore the lines after 'case "$C$4" ' are processed

            arCases = Array("X")
            Set shRows = Rows("36:77")

Next it matches the value in the target cell with the members in arCases and if no match, hides the rows of the range object shRows.

So coded like this, for every cell you want this hide/unhide to work just add a case like the other two cases (3 lines).