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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,202
Office Version
2007
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,202
Office Version
2007
Platform
Windows
(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>
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,202
Office Version
2007
Platform
Windows
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
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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 :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,202
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,455
Messages
5,486,998
Members
407,575
Latest member
calc

This Week's Hot Topics

Top