Multiple subs in VBA Code ?

[lmjannin]

Good morning.

I am working on the same spreadsheet described in this thread (https://www.mrexcel.com/forum/excel-questions/1112241-hiding-rows-using-vba-multiple-criteria.html)

It is a sheet where based on the answers provided in a set of dropdown boxes, certain rows need to be hidden entirely.

Per the thread listed above, I was able to get the first set to show/hide appropriately.

I need to add a second set of rules for a separate section on the workbook to hide/show rows in the same manner.

Using the same syntax as a part of the same sub, the second group of rules isn't working properly.

I am super new to VBA - I am hoping this is something simple that I haven't structured properly.

Please and thank you ahead of time I highlighted in red below the part that is not working.

THANK YOU!

Private Sub Worksheet_Change(ByVal Target As Range)
   
If Range("AA4") = "No" Then
   Rows("163:166").EntireRow.Hidden = True
Else
   Rows("163:166").EntireRow.Hidden = False
End If
    
If Range("AA5") = "No" Then
   Rows("167:168").EntireRow.Hidden = True
Else
   Rows("167:168").EntireRow.Hidden = False
End If


If Range("AA6") = "No" Then
   Rows("169:172").EntireRow.Hidden = True
Else
   Rows("169:172").EntireRow.Hidden = False
End If




If Not Intersect(Target, Range("AA3")) Is Nothing Then
    Me.Rows.Hidden = False
    
Select Case Range("AA3").Value
        Case 1
            Me.Rows("45:161").EntireRow.Hidden = True
        Case 2
            Me.Rows("58:161").EntireRow.Hidden = True
        Case 3
            Me.Rows("71:161").EntireRow.Hidden = True
        Case 4
            Me.Rows("84:161").EntireRow.Hidden = True
        Case 5
            Me.Rows("97:161").EntireRow.Hidden = True
        Case 6
            Me.Rows("110:161").EntireRow.Hidden = True
        Case 7
            Me.Rows("123:161").EntireRow.Hidden = True
        Case 8
            Me.Rows("136:161").EntireRow.Hidden = True
        Case 9
            Me.Rows("149:161").EntireRow.Hidden = True
        Case Else
            Me.Rows.Hidden = False


End Select
End If
End Sub


Private Sub Worksheet2_Change(ByVal Target As Range)






If Not Intersect(Target, Range("AA8")) Is Nothing Then
   Me.Rows.Hidden = False


Select Case Range("AA8").Value
   Case 1
        Me.Rows("190:413").EntireRow.Hidden = True
   Case 2
        Me.Rows("206:413").EntireRow.Hidden = True
   Case 3
        Me.Rows("222:413").EntireRow.Hidden = True
   Case 4
        Me.Rows("238:413").EntireRow.Hidden = True


   Case Else
        Me.Rows.Hidden = False


End Select
End If
End Sub

 

[Fluff]

You cannot change the name of the procedure as it's an in-built event.
Instead you need to add this

If Not Intersect(Target, Range("AA8")) Is Nothing Then
   Me.Rows.Hidden = False


Select Case Range("AA8").Value
   Case 1
        Me.Rows("190:413").EntireRow.Hidden = True
   Case 2
        Me.Rows("206:413").EntireRow.Hidden = True
   Case 3
        Me.Rows("222:413").EntireRow.Hidden = True
   Case 4
        Me.Rows("238:413").EntireRow.Hidden = True


   Case Else
        Me.Rows.Hidden = False


End Select
End If

Just before the End Sub line in the first set of code.

 

[lmjannin]

Thank you! I added the code where indicated, and that part of the code is now working. However when I added it, the first set of code above it stopped working. Now, regardless of what I populate in AA3, I am seeing all of the lines that I don't want to see.

This is the part of the code that is no longer working...that was my reason for trying to separate them. Thank you!

If Not Intersect(Target, Range("AA3")) Is Nothing Then
Me.Rows.Hidden = False

Select Case Range("AA3").Value
Case 1
Me.Rows("45:161").EntireRow.Hidden = True
Case 2
Me.Rows("58:161").EntireRow.Hidden = True
Case 3
Me.Rows("71:161").EntireRow.Hidden = True
Case 4
Me.Rows("84:161").EntireRow.Hidden = True
Case 5
Me.Rows("97:161").EntireRow.Hidden = True
Case 6
Me.Rows("110:161").EntireRow.Hidden = True
Case 7
Me.Rows("123:161").EntireRow.Hidden = True
Case 8
Me.Rows("136:161").EntireRow.Hidden = True
Case 9
Me.Rows("149:161").EntireRow.Hidden = True
Case Else
Me.Rows.Hidden = False

​

 

[Fluff]

Try it like

If Not Intersect(Target, Range("AA3")) Is Nothing Then
    Me.Rows("45:161").Hidden = False
    
    Select Case Range("AA3").Value
            Case 1
                Me.Rows("45:161").EntireRow.Hidden = True
            Case 2
                Me.Rows("58:161").EntireRow.Hidden = True
            Case 3
                Me.Rows("71:161").EntireRow.Hidden = True
            Case 4
                Me.Rows("84:161").EntireRow.Hidden = True
            Case 5
                Me.Rows("97:161").EntireRow.Hidden = True
            Case 6
                Me.Rows("110:161").EntireRow.Hidden = True
            Case 7
                Me.Rows("123:161").EntireRow.Hidden = True
            Case 8
                Me.Rows("136:161").EntireRow.Hidden = True
            Case 9
                Me.Rows("149:161").EntireRow.Hidden = True
    End Select
ElseIf Not Intersect(Target, Range("AA8")) Is Nothing Then
    Me.Rows("190:143").Hidden = False

    Select Case Range("AA8").Value
       Case 1
            Me.Rows("190:413").EntireRow.Hidden = True
       Case 2
            Me.Rows("206:413").EntireRow.Hidden = True
       Case 3
            Me.Rows("222:413").EntireRow.Hidden = True
       Case 4
            Me.Rows("238:413").EntireRow.Hidden = True
    End Select
End If

 

[lmjannin]

This worked like a charm. Thank you very, very much for your help!

 

[Fluff]

You're welcome & thanks for the feedback