hiding rows based on cell content (multiple options)

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
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?
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Give this a try
Code:
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
 
Upvote 0
Sorry, fotget the previous reply it is wrong. This one does work
Code:
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
 
Upvote 0
thank you ..... it works but for case "$H$4", I need the rows ("36:77") to be hidden....how do you do that?
 
Upvote 0
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

Code:
            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).
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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