AW: VBA: Bedingtes Ausblenden von Zeilen
18.09.2015 15:49:50
Zeilen
Hi Nikolaj
Mach ein ganz normales Makro und weise es (rechte Maustaste, Makro zuweisen) den Dropdownlisten zu.
https://www.herber.de/bbs/user/100262.xlsm
Sub BeiÄnderung()
On Error GoTo Fehler
Rows("21:1001").EntireRow.Hidden = True
Select Case Range("Kombi") 'definierter Name aus D1
Case Is = 249, 259
Rows("21:36").EntireRow.Hidden = False
Case Is = 3223
Rows("37:52").EntireRow.Hidden = False
Case Is = 3246, 4212, 4312, 4412
Rows("53:68").EntireRow.Hidden = False
Case Is = 2410, 2510, 3229, 3314, 3414, 3514
Rows("69:84").EntireRow.Hidden = False
Case Is = 222, 232, 242, 252
Rows("85:100").EntireRow.Hidden = False
Case Is = 223, 233, 243, 253, 327, 332, 342, 352
Rows("101:116").EntireRow.Hidden = False
Case Is = 323, 333, 343, 353, 422, 432, 442
Rows("117:132").EntireRow.Hidden = False
Case Is = 324
Rows("133:148").EntireRow.Hidden = False
Case Is = 325, 334, 344, 354
Rows("149:164").EntireRow.Hidden = False
Case Is = 326, 335, 345, 355
Rows("165:180").EntireRow.Hidden = False
Case Is = 327
Rows("181:196").EntireRow.Hidden = False
Case Is = 328, 423, 433, 443
Rows("197:211").EntireRow.Hidden = False
Case Is = 329
Rows("212:227").EntireRow.Hidden = False
Case Is = 3210, 336, 346, 356, 424, 434, 444
Rows("228:243").EntireRow.Hidden = False
Case Is = 2411, 2511
Rows("244:260").EntireRow.Hidden = False
Case Is = 224, 234, 244, 254
Rows("261:276").EntireRow.Hidden = False
Case Is = 3211, 337, 347, 357
Rows("277:292").EntireRow.Hidden = False
Case Is = 225, 235, 245, 255
Rows("293:308").EntireRow.Hidden = False
Case Is = 3212, 338, 348, 358, 425, 435, 445
Rows("309:324").EntireRow.Hidden = False
Case Is = 3213
Rows("325:340").EntireRow.Hidden = False
Case Is = 3214, 339, 349, 359
Rows("341:356").EntireRow.Hidden = False
Case Is = 3215, 426, 436, 446
Rows("357:372").EntireRow.Hidden = False
Case Is = 3216
Rows("373:388").EntireRow.Hidden = False
Case Is = 427, 437, 447
Rows("389:404").EntireRow.Hidden = False
Case Is = 3217, 3310, 3410, 3510, 428, 438, 448
Rows("405:420").EntireRow.Hidden = False
Case Is = 3218
Rows("421:436").EntireRow.Hidden = False
Case Is = 3230, 3315, 3415, 3515
Rows("453:468").EntireRow.Hidden = False
Case Is = 3231, 3316, 3416, 3516
Rows("469:482").EntireRow.Hidden = False
Case Is = 2412, 2515
Rows("483:498").EntireRow.Hidden = False
Case Is = 229, 239, 2413, 2513, 3232, 3317, 3417, 3517
Rows("499:514").EntireRow.Hidden = False
Case Is = 3233, 3318, 3418, 3518, 4218, 4318, 4418
Rows("515:530").EntireRow.Hidden = False
Case Is = 3244, 3328, 3428, 3528
Rows("531:546").EntireRow.Hidden = False
Case Is = 3245, 3329, 3429, 3529
Rows("547:562").EntireRow.Hidden = False
Case Is = 3240, 3324, 3424, 3524
Rows("563:578").EntireRow.Hidden = False
Case Is = 2414, 2514, 3234, 3319, 3419, 3519
Rows("579:594").EntireRow.Hidden = False
Case Is = 3224, 4213, 4313, 4413, 3235, 4219, 4319, 4419
Rows("595:610").EntireRow.Hidden = False
Case Is = 3225
Rows("611:626").EntireRow.Hidden = False
Case Is = 4214, 4314, 4414
Rows("627:642").EntireRow.Hidden = False
Case Is = 2214, 2314, 2419, 2519, 3249, 3333, 3433, 3533
Rows("643:658").EntireRow.Hidden = False
Case Is = 3251, 3335, 3435, 3535
Rows("659:674").EntireRow.Hidden = False
Case Is = 3252, 3336, 3436, 3536
Rows("675:690").EntireRow.Hidden = False
Case Is = 3253, 3337, 3437, 3537
Rows("691:706").EntireRow.Hidden = False
Case Is = 3250, 3334, 3434, 3534
Rows("707:722").EntireRow.Hidden = False
Case Is = 2210, 2310, 2415, 2515
Rows("723:738").EntireRow.Hidden = False
Case Is = 3236, 3320, 3240, 3520, 4236, 4336, 4436
Rows("739:754").EntireRow.Hidden = False
Case Else
Rows("986:1001").EntireRow.Hidden = False
End Select
Exit Sub
Fehler:
Rows("21:1001").EntireRow.Hidden = False
End Sub
(lediglich die If-Abfrage für Intersect entfernen)
cu
Chris