Multiple subs in VBA Code ?

lmjannin

New Member
Joined
Oct 12, 2019
Messages
7
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!

Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You cannot change the name of the procedure as it's an in-built event.
Instead you need to add this
Code:
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.
 
Upvote 0
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

 
Upvote 0
Try it like
Code:
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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