# Thread: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists Thanks:  2 Post #5274591 (1)Post #5241501 (1) Likes:  4 Post #5275874 (1)Post #5241501 (1)Post #5242506 (1)Post #5274591 (1)

1. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

Try this. The line in blue must be modified

Originally Posted by DanteAmor
Try this

Code:
```Private Sub Worksheet_Change(ByVal Target As Range)

Dim InvSel As Variant, PartSel As Variant, part1 As Variant, part2 As Variant
Dim InvestRng As Range, PartnerRng As Range, r As Range, twoRng As Range
Dim waum As Long, ini1 As Long, fin1 As Long, k As Long

If Target.Count > 2 Then Exit Sub

Set InvestRng = Range("No._Investments")
Set PartnerRng = Range("No._Partners")
Set twoRng = Union(InvestRng, PartnerRng)
If Not Intersect(Target, twoRng) Is Nothing Then

Rows("163:292").EntireRow.Hidden = False
part1 = Array(163, , 165, , 191, , 217, , 243, , 269)
part2 = Array(292, , 173, , 199, , 225, , 251, , 277)

InvSel = IIf(InvestRng.Value = "Please Select", 0, InvestRng.Value)
PartSel = PartnerRng.Value
If InvSel = 0 Then
Set r = Range(part1(0) & ":" & part2(0))
Else
For k = 2 To InvSel Step 2
If PartSel = "Please Select" Then waum = 0 Else waum = PartSel - 1
ini1 = part1(k) + waum
fin1 = part2(k)
If fin1 >= ini1 Then
If r Is Nothing Then
Set r = Union(Range(ini1 & ":" & fin1), Range(ini1 + 13 & ":" & fin1 + 13))
Else
Set r = Union(r, Range(ini1 & ":" & fin1), Range(ini1 + 13 & ":" & fin1 + 13))
End If
End If
Next
End If

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```

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

Hi Dante. I really appreciate you taking the time to look at this code again.
It is not from lack of trying on my side but I just can't seem to get it to behave correctly. I read your notes carefully and tried changing numerous things, but the best I can do, is to get it working on even numbers only.
You'll see I have put in all numbers from 1-10 under Investments and placed conditional formatting to hide sections which are not required (for example 3 selected, conditional formatting hides number 4).
Also, under the journal section, it is hiding rows 295:304 which doesn't even come into play.
Would you mind taking a look please??? If I have to work with even numbers only, I will, but it would be great if it could work on all investment numbers. Thank you!!

https://www.dropbox.com/s/r65pssyxpu...ante.xlsm?dl=0

3. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

I will try to review, but there are more than 80 tests to perform, I do not have the time to try everything, so I only review some of them and they work.

4. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

I tried adding to the arrays so it covered all investments numbers (even and odd) but that didn't do the trick.

Rows("163:292").EntireRow.Hidden = False
part1 = Array(163, 165, 178, 191, 204, 217, 230, 243, 256, 269, 282)
part2 = Array(292, 173, 186, 199, 212, 225, 238, 251, 264, 277, 290)

Sorry, I hadn't realised it was so in-depth. If you don't get the time, don't worry about it, I'll just go back to using even numbers.

5. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

You can give me all the combinations and which rows should be hidden.
0-0 - what rows are hidden
0-2 - what rows are hidden
0-3 - what rows are hidden
...
2-0 - what rows are hidden
2-2 - what rows are hidden
2-3 - what rows are hidden
2-4- what rows are hidden
...
4-0 - what rows are hidden
4-2 - what rows are hidden
4-3 - what rows are hidden
4-4- what rows are hidden
...
and continue
.

Do you have other macros running?

6. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

I now totally appreciate what you mean by many tests to run! Below is the detail of the rows which should hide depending on the combined outcome of Investment and Partner.
I have other VBA coding which hides/unhides rows above the journal section depending on Investment selection and another for Partner selection. I also have VBA coding on this sheet which hides/unhides another sheet (K1a) depending on another drop down selection. The picture icon at the top left hand corner takes you to the Home Page sheet.
Eventually, there will be a Posting Macro which will take the final journal figures (B157:M292) and paste them onto another sheet. There are also macros on other sheets within the workbook.
Thank you!

Code:
```Investment
Partner
Rows Hidden

*All
163:292
176:292

0
*All
163:292
176:292

1
165:173
176:292

1
0
165:173
176:292

1
2
166:173
176:292

1
3
167:173
176:292

1
4
168:173
176:292

1
5
169:173
176:292

1
6
170:173
176:292

1
7
171:173
176:292

1
8
172:173
176:292

1
9
173:173
176:292

1
10

176:292

2
165:173
178:186
189:292

2
0
165:173
178:186
189:292

2
2
166:173
179:186
189:292

2
3
167:173
180:186
189:292

2
4
168:173
181:186
189:292

2
5
169:173
182:186
189:292

2
6
170:173
183:186
189:292

2
7
171:173
184:186
189:292

2
8
172:173
185:186
189:292

2
9
173:173
186:186
189:292

2
10

189:292

3
165:173
178:186
191:199
202:292

3
0
165:173
178:186
191:199
202:292

3
2
166:173
179:186
192:199
202:292

3
3
167:173
180:186
193:199
202:292

3
4
168:173
181:186
194:199
202:292

3
5
169:173
182:186
195:199
202:292

3
6
170:173
183:186
196:199
202:292

3
7
171:173
184:186
197:199
202:292

3
8
172:173
185:186
198:199
202:292

3
9
173:173
186:186
199:199
202:292

3
10

202:292

4
165:173
178:186
191:199
204:212
215:292

4
0
165:173
178:186
191:199
204:212
215:292

4
2
166:173
179:186
192:199
205:212
215:292

4
3
167:173
180:186
193:199
206:212
215:292

4
4
168:173
181:186
194:199
207:212
215:292

4
5
169:173
182:186
195:199
208:212
215:292

4
6
170:173
183:186
196:199
209:212
215:292

4
7
171:173
184:186
197:199
210:212
215:292

4
8
172:173
185:186
198:199
211:212
215:292

4
9
173:173
186:186
199:199
212:212
215:292

4
10

215:292

5
165:173
178:186
191:199
204:212
217:225
228:292

5
0
165:173
178:186
191:199
204:212
217:225
228:292

5
2
166:173
179:186
192:199
205:212
218:225
228:292

5
3
167:173
180:186
193:199
206:212
219:225
228:292

5
4
168:173
181:186
194:199
207:212
220:225
228:292

5
5
169:173
182:186
195:199
208:212
221:225
228:292

5
6
170:173
183:186
196:199
209:212
222:225
228:292

5
7
171:173
184:186
197:199
210:212
223:225
228:292

5
8
172:173
185:186
198:199
211:212
224:225
228:292

5
9
173:173
186:186
199:199
212:212
225:225
228:292

5
10

228:292

6
165:173
178:186
191:199
204:212
217:225
230:238
241:292

6
0
165:173
178:186
191:199
204:212
217:225
230:238
241:292

6
2
166:173
179:186
192:199
205:212
218:225
231:238
241:292

6
3
167:173
180:186
193:199
206:212
219:225
232:238
241:292

6
4
168:173
181:186
194:199
207:212
220:225
233:238
241:292

6
5
169:173
182:186
195:199
208:212
221:225
234:238
241:292

6
6
170:173
183:186
196:199
209:212
222:225
235:238
241:292

6
7
171:173
184:186
197:199
210:212
223:225
236:238
241:292

6
8
172:173
185:186
198:199
211:212
224:225
237:238
241:292

6
9
173:173
186:186
199:199
212:212
225:225
238:238
241:292

6
10

241:292

7
165:173
178:186
191:199
204:212
217:225
230:238
243:251
254:292

7
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
254:292

7
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
254:292

7
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
254:292

7
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
254:292

7
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
254:292

7
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
254:292

7
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
254:292

7
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
254:292

7
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
254:292

7
10

254:292

8
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
267:292

8
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
267:292

8
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
257:264
267:292

8
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
258:264
267:292

8
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
259:264
267:292

8
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
260:264
267:292

8
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
261:264
267:292

8
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
262:264
267:292

8
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
262:264
267:292

8
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
264:264
267:292

8
10

267:292

9
165:173
178:186
191:199
204:212
217:225
230:238
243:251
269:277
280:292

9
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
269:277
280:292

9
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
270:277
280:292

9
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
271:277
280:292

9
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
272:277
280:292

9
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
273:277
280:292

9
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
274:277
280:292

9
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
275:277
280:292

9
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
276:277
280:292

9
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
277:277
280:292

9
10

280:292

10
165:173
178:186
191:199
204:212
217:225
230:238
243:251
269:277
282:290

10
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
269:277
282:290

10
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
270:277
283:290

10
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
271:277
284:290

10
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
272:277
285:290

10
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
273:277
286:290

10
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
274:277
287:290

10
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
275:277
288:290

10
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
276:277
289:290

10
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
277:277
290:290

```

7. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

I just noticed Investment 10, Partner 10 dropped off the end. If selected, no rows to hide.

8. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

Sorry!! I just realised I omitted two sections from 9 and 10. Revised/correct below..

Code:
```Investment
Partner
Rows Hidden

*All
163:292
176:292

0
*All
163:292
176:292

1
165:173
176:292

1
0
165:173
176:292

1
2
166:173
176:292

1
3
167:173
176:292

1
4
168:173
176:292

1
5
169:173
176:292

1
6
170:173
176:292

1
7
171:173
176:292

1
8
172:173
176:292

1
9
173:173
176:292

1
10

176:292

2
165:173
178:186
189:292

2
0
165:173
178:186
189:292

2
2
166:173
179:186
189:292

2
3
167:173
180:186
189:292

2
4
168:173
181:186
189:292

2
5
169:173
182:186
189:292

2
6
170:173
183:186
189:292

2
7
171:173
184:186
189:292

2
8
172:173
185:186
189:292

2
9
173:173
186:186
189:292

2
10

189:292

3
165:173
178:186
191:199
202:292

3
0
165:173
178:186
191:199
202:292

3
2
166:173
179:186
192:199
202:292

3
3
167:173
180:186
193:199
202:292

3
4
168:173
181:186
194:199
202:292

3
5
169:173
182:186
195:199
202:292

3
6
170:173
183:186
196:199
202:292

3
7
171:173
184:186
197:199
202:292

3
8
172:173
185:186
198:199
202:292

3
9
173:173
186:186
199:199
202:292

3
10

202:292

4
165:173
178:186
191:199
204:212
215:292

4
0
165:173
178:186
191:199
204:212
215:292

4
2
166:173
179:186
192:199
205:212
215:292

4
3
167:173
180:186
193:199
206:212
215:292

4
4
168:173
181:186
194:199
207:212
215:292

4
5
169:173
182:186
195:199
208:212
215:292

4
6
170:173
183:186
196:199
209:212
215:292

4
7
171:173
184:186
197:199
210:212
215:292

4
8
172:173
185:186
198:199
211:212
215:292

4
9
173:173
186:186
199:199
212:212
215:292

4
10

215:292

5
165:173
178:186
191:199
204:212
217:225
228:292

5
0
165:173
178:186
191:199
204:212
217:225
228:292

5
2
166:173
179:186
192:199
205:212
218:225
228:292

5
3
167:173
180:186
193:199
206:212
219:225
228:292

5
4
168:173
181:186
194:199
207:212
220:225
228:292

5
5
169:173
182:186
195:199
208:212
221:225
228:292

5
6
170:173
183:186
196:199
209:212
222:225
228:292

5
7
171:173
184:186
197:199
210:212
223:225
228:292

5
8
172:173
185:186
198:199
211:212
224:225
228:292

5
9
173:173
186:186
199:199
212:212
225:225
228:292

5
10

228:292

6
165:173
178:186
191:199
204:212
217:225
230:238
241:292

6
0
165:173
178:186
191:199
204:212
217:225
230:238
241:292

6
2
166:173
179:186
192:199
205:212
218:225
231:238
241:292

6
3
167:173
180:186
193:199
206:212
219:225
232:238
241:292

6
4
168:173
181:186
194:199
207:212
220:225
233:238
241:292

6
5
169:173
182:186
195:199
208:212
221:225
234:238
241:292

6
6
170:173
183:186
196:199
209:212
222:225
235:238
241:292

6
7
171:173
184:186
197:199
210:212
223:225
236:238
241:292

6
8
172:173
185:186
198:199
211:212
224:225
237:238
241:292

6
9
173:173
186:186
199:199
212:212
225:225
238:238
241:292

6
10

241:292

7
165:173
178:186
191:199
204:212
217:225
230:238
243:251
254:292

7
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
254:292

7
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
254:292

7
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
254:292

7
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
254:292

7
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
254:292

7
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
254:292

7
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
254:292

7
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
254:292

7
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
254:292

7
10

254:292

8
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
267:292

8
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
267:292

8
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
257:264
267:292

8
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
258:264
267:292

8
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
259:264
267:292

8
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
260:264
267:292

8
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
261:264
267:292

8
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
262:264
267:292

8
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
262:264
267:292

8
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
264:264
267:292

8
10

267:292

9
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
269:277
280:292

9
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
269:277
280:292

9
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
257:264
270:277
280:292

9
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
258:264
271:277
280:292

9
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
259:264
272:277
280:292

9
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
260:264
273:277
280:292

9
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
261:264
274:277
280:292

9
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
262:264
275:277
280:292

9
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
262:264
276:277
280:292

9
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
264:264
277:277
280:292

9
10

280:292

10
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
269:277
282:290

10
0
165:173
178:186
191:199
204:212
217:225
230:238
243:251
256:264
269:277
282:290

10
2
166:173
179:186
192:199
205:212
218:225
231:238
244:251
257:264
270:277
283:290

10
3
167:173
180:186
193:199
206:212
219:225
232:238
245:251
258:264
271:277
284:290

10
4
168:173
181:186
194:199
207:212
220:225
233:238
246:251
259:264
272:277
285:290

10
5
169:173
182:186
195:199
208:212
221:225
234:238
247:251
260:264
273:277
286:290

10
6
170:173
183:186
196:199
209:212
222:225
235:238
248:251
261:264
274:277
287:290

10
7
171:173
184:186
197:199
210:212
223:225
236:238
249:251
262:264
275:277
288:290

10
8
172:173
185:186
198:199
211:212
224:225
237:238
250:251
262:264
276:277
289:290

10
9
173:173
186:186
199:199
212:212
225:225
238:238
251:251
264:264
277:277
290:290

```

9. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

Do you have a macro that hides rows?

I have other VBA coding which hides / unhides rows above the journal section depending on Investment selection and another for Partner selection.

Could you remove that macro and only test my macro and tell me where the fault is?

10. ## Re: VBA Code Not Working / Error - Hide rows depending on 2x drop down lists

Your previous code with even numbers works the best for me, so I have decided to go back to that.
However, is it easy enough that if investment 1 is selected, settings for investment 2 is applied.
Eg.
Investment
1 or 2 - applies No.2 settings
3 or 4 - applies No.4 setting
5 or 6 - applies No.6 settings
7 or 8 - applies No.8 settings
9 or 10 - applies No.10 settings

Just so the user can choose an odd number of investments, even though the even number settings/visibility are applied. If not easy, don't worry!!

I'm going back to using this code..

Code:
```Private Sub Worksheet_Change(ByVal Target As Range)

Dim InvSel As Variant, PartSel As Variant, part1 As Variant, part2 As Variant
Dim InvestRng As Range, PartnerRng As Range, r As Range, twoRng As Range
Dim waum As Long, ini1 As Long, fin1 As Long, k As Long

If Target.Count > 2 Then Exit Sub

Set InvestRng = Range("No._Investments")
Set PartnerRng = Range("No._Partners")
Set twoRng = Union(InvestRng, PartnerRng)
If Not Intersect(Target, twoRng) Is Nothing Then

Rows("163:292").EntireRow.Hidden = False
part1 = Array(163, , 165, , 191, , 217, , 243, , 269)
part2 = Array(292, , 173, , 199, , 225, , 251, , 277)

InvSel = IIf(InvestRng.Value = "Please Select", 0, InvestRng.Value)
PartSel = PartnerRng.Value
If InvSel = 0 Then
Set r = Range(part1(0) & ":" & part2(0))
Else
For k = 2 To InvSel Step 2
If PartSel = "Please Select" Then waum = 0 Else waum = PartSel - 1
ini1 = part1(k) + waum
fin1 = part2(k)
If fin1 > ini1 Then
If r Is Nothing Then
Set r = Union(Range(ini1 & ":" & fin1), Range(ini1 + 13 & ":" & fin1 + 13))
Else
Set r = Union(r, Range(ini1 & ":" & fin1), Range(ini1 + 13 & ":" & fin1 + 13))
End If
End If
Next
End If

If Not r Is Nothing Then r.EntireRow.Hidden = True
End If```