VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
Hi there,

Before I go back to the drawing board, can anyone see whether they can get this VBA code to work?

Basically, I have two drop down lists. One is "No. of Investments" and the other is "No. of Partners". I would like certain rows to hide, depending on the result of both drop down lists.

Each case below shows the change in "No. of Investments" and then looks at the drop down list of "No. of Partners" and hides the rows accordingly (well, it's supposed to atleast).

Often, drop down list "No. of Partners" will not be engaged and will remain on "Please Select", so the first line of code will be used depending on the "No. of Investments" selected.

Currently the error being returned is "Compile Error - Argument no Optional" and it highlights the word "Union" on the first "Set R" line.

Drop down lists look like this;
No._Investments = Please Select,0,2,4,6,8,10
No._Partners = Please Select,2,3,4,5,6,7,8,9,10

Thanks for looking.


Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim i As Range
  Dim R As Range
  Dim InvSel As Integer
  Dim PartSel As Integer
  Dim InvestRng As Range
  Dim PartnerRng As Range
  
  Set InvestRng = Range("No._Investments")
  Set PartnerRng = Range("No._Partners")
  
  Set i = Intersect(Target, InvestRng)
  If Not i Is Nothing Then
    InvSel = InvestRng.Value
    PartSel = PartnerRng.Value
    Select Case InvSel
      Case "Please Select"
        If PartSel = "Please Select" Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("163:292"))
        End If
     Case 0
        If PartSel = "Please Select" Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("163:292"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("163:292"))
        End If
      Case 2
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"))
        End If
      
      Case 4
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"))
        End If
      Case 6
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"))
        End If
      Case 8
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("232:238"), Range("245:251"), Range("258:264"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("233:238"), Range("246:251"), Range("259:264"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("234:238"), Range("247:251"), Range("260:264"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("235:238"), Range("248:251"), Range("261:264"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("236:238"), Range("249:251"), Range("262:264"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("237:238"), Range("250:251"), Range("263:264"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"))
        End If
      Case 10
        If PartSel = "Please Select" Then
          Set R = Union(Range("165:173"), Range("178:186"), Range("191:199"), Range("204:212"), Range("217:225"), Range("230:238"), Range("243:251"), Range("256:264"), Range("269:277"), Range("282:290"))
        ElseIf PartSel = 2 Then
          Set R = Union(Range("166:173"), Range("179:186"), Range("192:199"), Range("205:212"), Range("218:225"), Range("231:238"), Range("244:251"), Range("257:264"), Range("270:277"), Range("283:290"))
        ElseIf PartSel = 3 Then
          Set R = Union(Range("167:173"), Range("180:186"), Range("193:199"), Range("206:212"), Range("219:225"), Range("231:238"), Range("245:251"), Range("258:264"), Range("271:277"), Range("284:290"))
        ElseIf PartSel = 4 Then
          Set R = Union(Range("168:173"), Range("181:186"), Range("194:199"), Range("207:212"), Range("220:225"), Range("231:238"), Range("246:251"), Range("259:264"), Range("272:277"), Range("285:290"))
        ElseIf PartSel = 5 Then
          Set R = Union(Range("169:173"), Range("182:186"), Range("195:199"), Range("208:212"), Range("221:225"), Range("231:238"), Range("247:251"), Range("260:264"), Range("273:277"), Range("286:290"))
        ElseIf PartSel = 6 Then
          Set R = Union(Range("170:173"), Range("183:186"), Range("196:199"), Range("209:212"), Range("222:225"), Range("231:238"), Range("248:251"), Range("261:264"), Range("274:277"), Range("287:290"))
        ElseIf PartSel = 7 Then
          Set R = Union(Range("171:173"), Range("184:186"), Range("197:199"), Range("210:212"), Range("223:225"), Range("231:238"), Range("249:251"), Range("262:264"), Range("275:277"), Range("288:290"))
        ElseIf PartSel = 8 Then
          Set R = Union(Range("172:173"), Range("185:186"), Range("198:199"), Range("211:212"), Range("224:225"), Range("231:238"), Range("250:251"), Range("263:264"), Range("276:277"), Range("289:290"))
        ElseIf PartSel = 9 Then
          Set R = Union(Range("173"), Range("186"), Range("199"), Range("212"), Range("225"), Range("238"), Range("251"), Range("264"), Range("277"), Range("290"))
     End If
    End Select
      
    If Not R Is Nothing Then R.EntireRow.Hidden = True
  End If
       
          
If [h7] = "YES" Then
Sheets("K1a").Visible = True
Else
Sheets("K1a").Visible = False
End If
End Sub
 
I can make it work for everyone, but if you are running another macro besides of mine that also hides rows, then we will never finish.


I'm going to review the row ranges that should be hidden with the data you sent me, I'm going to try all the combinations and give you the macro.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
(My macro worked, but you changed the numbering of the original requirement :()

Plese confirm this

InvestmentPartnerRows Hidden
Please Select*All163:292176:292*All includes: Please Select, 2-10
0*All163:292176:292*All includes: Please Select, 2-10
1Please Select165:173176:292
10165:173176:292
12166:173176:292
13167:173176:292
14168:173176:292
15169:173176:292
16170:173176:292
17171:173176:292
18172:173176:292
19173:173176:292
110176:292
2Please Select165:173178:186189:292
20165:173178:186189:292
22166:173179:186189:292
23167:173180:186189:292
24168:173181:186189:292
25169:173182:186189:292
26170:173183:186189:292
27171:173184:186189:292
28172:173185:186189:292
29173:173186:186189:292
210189:292
3Please Select165:173178:186191:199202:292
30165:173178:186191:199202:292
32166:173179:186192:199202:292
33167:173180:186193:199202:292
34168:173181:186194:199202:292
35169:173182:186195:199202:292
36170:173183:186196:199202:292
37171:173184:186197:199202:292
38172:173185:186198:199202:292
39173:173186:186199:199202:292
310202:292
4Please Select165:173178:186191:199204:212215:292
40165:173178:186191:199204:212215:292
42166:173179:186192:199205:212215:292
43167:173180:186193:199206:212215:292
44168:173181:186194:199207:212215:292
45169:173182:186195:199208:212215:292
46170:173183:186196:199209:212215:292
47171:173184:186197:199210:212215:292
48172:173185:186198:199211:212215:292
49173:173186:186199:199212:212215:292
410215:292
5Please Select165:173178:186191:199204:212217:225228:292
50165:173178:186191:199204:212217:225228:292
52166:173179:186192:199205:212218:225228:292
53167:173180:186193:199206:212219:225228:292
54168:173181:186194:199207:212220:225228:292
55169:173182:186195:199208:212221:225228:292
56170:173183:186196:199209:212222:225228:292
57171:173184:186197:199210:212223:225228:292
58172:173185:186198:199211:212224:225228:292
59173:173186:186199:199212:212225:225228:292
510228:292
6Please Select165:173178:186191:199204:212217:225230:238241:292
60165:173178:186191:199204:212217:225230:238241:292
62166:173179:186192:199205:212218:225231:238241:292
63167:173180:186193:199206:212219:225232:238241:292
64168:173181:186194:199207:212220:225233:238241:292
65169:173182:186195:199208:212221:225234:238241:292
66170:173183:186196:199209:212222:225235:238241:292
67171:173184:186197:199210:212223:225236:238241:292
68172:173185:186198:199211:212224:225237:238241:292
69173:173186:186199:199212:212225:225238:238241:292
610241:292
7Please Select165:173178:186191:199204:212217:225230:238243:251254:292
70165:173178:186191:199204:212217:225230:238243:251254:292
72166:173179:186192:199205:212218:225231:238244:251254:292
73167:173180:186193:199206:212219:225232:238245:251254:292
74168:173181:186194:199207:212220:225233:238246:251254:292
75169:173182:186195:199208:212221:225234:238247:251254:292
76170:173183:186196:199209:212222:225235:238248:251254:292
77171:173184:186197:199210:212223:225236:238249:251254:292
78172:173185:186198:199211:212224:225237:238250:251254:292
79173:173186:186199:199212:212225:225238:238251:251254:292
710254:292
8Please Select165:173178:186191:199204:212217:225230:238243:251256:264267:292
80165:173178:186191:199204:212217:225230:238243:251256:264267:292
82166:173179:186192:199205:212218:225231:238244:251257:264267:292
83167:173180:186193:199206:212219:225232:238245:251258:264267:292
84168:173181:186194:199207:212220:225233:238246:251259:264267:292
85169:173182:186195:199208:212221:225234:238247:251260:264267:292
86170:173183:186196:199209:212222:225235:238248:251261:264267:292
87171:173184:186197:199210:212223:225236:238249:251262:264267:292
88172:173185:186198:199211:212224:225237:238250:251262:264267:292
89173:173186:186199:199212:212225:225238:238251:251264:264267:292
810267:292
9Please Select165:173178:186191:199204:212217:225230:238243:251256:264269:277280:292
90165:173178:186191:199204:212217:225230:238243:251256:264269:277280:292
92166:173179:186192:199205:212218:225231:238244:251257:264270:277280:292
93167:173180:186193:199206:212219:225232:238245:251258:264271:277280:292
94168:173181:186194:199207:212220:225233:238246:251259:264272:277280:292
95169:173182:186195:199208:212221:225234:238247:251260:264273:277280:292
96170:173183:186196:199209:212222:225235:238248:251261:264274:277280:292
97171:173184:186197:199210:212223:225236:238249:251262:264275:277280:292
98172:173185:186198:199211:212224:225237:238250:251262:264276:277280:292
99173:173186:186199:199212:212225:225238:238251:251264:264277:277280:292
910280:292
10Please Select165:173178:186191:199204:212217:225230:238243:251256:264269:277282:290
100165:173178:186191:199204:212217:225230:238243:251256:264269:277282:290
102166:173179:186192:199205:212218:225231:238244:251257:264270:277283:290
103167:173180:186193:199206:212219:225232:238245:251258:264271:277284:290
104168:173181:186194:199207:212220:225233:238246:251259:264272:277285:290
105169:173182:186195:199208:212221:225234:238247:251260:264273:277286:290
106170:173183:186196:199209:212222:225235:238248:251261:264274:277287:290
107171:173184:186197:199210:212223:225236:238249:251262:264275:277288:290
108172:173185:186198:199211:212224:225237:238250:251262:264276:277289:290
109173:173186:186199:199212:212225:225238:238251:251264:264277:277290:290

<tbody>
</tbody>
 
Upvote 0
Hi Dante. Sorry for the delay! I've only just seen this now. Thank you for looking in to it further.
I have checked all the items in red and yes, they are correct. The only thing, is that "0" is not an option under No. of Partners. It goes "Please Select" (which is equal to "0") and then 2. I don't think it makes much difference.
If I can get this running smoothly that would be really great!! Thanks again.
 
Upvote 0
Try this

Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim InvSel As Variant, PartSel As Variant, part1 As Variant, part2 As Variant, part3 As Variant
    Dim r As Range, waum As Long, ini1 As Long, fin1 As Long, k As Long
    
    If Target.Count > 2 Then Exit Sub
    
    If Not Intersect(Target, Range("No._Investments", "No._Partners")) Is Nothing Then
        Rows("163:292").EntireRow.Hidden = False
        InvSel = IIf(Range("No._Investments").Value = "Please Select", 0, Range("No._Investments").Value)
        PartSel = IIf(Range("No._Partners").Value = "Please Select", 0, Range("No._Partners").Value)
        
        part1 = Array(, 165, 178, 191, 204, 217, 230, 243, 256, 269, 282)
        part2 = Array(, 173, 186, 199, 212, 225, 238, 251, 264, 277, 290)
        part3 = Array(, 176, 189, 202, 215, 228, 241, 254, 267, 280)
        
        If InvSel = 0 Then
            Set r = Range(163 & ":" & 292)
        Else
            If PartSel < 10 Then
                For k = 1 To InvSel
                    If PartSel = 0 Then waum = 0 Else waum = PartSel - 1
                    ini1 = part1(k) + waum
                    fin1 = part2(k)
                    If r Is Nothing Then
                        Set r = Range(ini1 & ":" & fin1)
                    Else
                        Set r = Union(r, Range(ini1 & ":" & fin1))
                    End If
                Next
                If InvSel < 10 Then Set r = Union(r, Range(part3(InvSel) & ":" & 292))
            Else
                If InvSel < 10 Then Set r = Range(part3(InvSel) & ":" & 292)
            End If
        End If
        If Not r Is Nothing Then r.EntireRow.Hidden = True
    End If
End Sub
 
Upvote 0
Thank you for taking the time to do this for me!
It's working beautifully now and is one of the best features of my spreadsheet!
I really appreciate it. Muchas gracias :)
 
Upvote 0
Thank you for taking the time to do this for me!
It's working beautifully now and is one of the best features of my spreadsheet!
I really appreciate it. Muchas gracias :)

I'm glad to help you. I appreciate your kind comments.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
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