VBA Code Not Working / Error - Hide rows depending on 2x drop down lists
Page 4 of 4 FirstFirst ... 234
Results 31 to 36 of 36

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

  1. #31
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,263
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    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.
    Regards Dante Amor

  2. #32
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,263
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    (My macro worked, but you changed the numbering of the original requirement )

    Plese confirm this

    Investment Partner Rows Hidden
    Please Select *All 163:292 176:292 *All includes: Please Select, 2-10
    0 *All 163:292 176:292 *All includes: Please Select, 2-10
    1 Please Select 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 Please Select 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 Please Select 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 Please Select 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 Please Select 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 Please Select 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 Please Select 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 Please Select 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 Please Select 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 Please Select 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
    Regards Dante Amor

  3. #33
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #34
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,263
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    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
    Regards Dante Amor

  5. #35
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #36
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,263
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by tlc53 View Post
    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •