VBA Vereinfachung
18.02.2019 07:51:03
Gergö
Brauch wieder einmal eure Hilfe,
Ich habe aus unterschiedlichen FOhren einen funktionierenden VBA Text erstellt, jedoch rechnet mein PC ziemlich lange.
Gibt es möglichkeiten, Wege den VBA Text zu vereinfachen um schnellere Ergebnise zu erhalten?
Hier der Text
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Erstellt in Bereich A10 bei Klick ein leeres K?stchen oder ein angehaktes K?stchen
Select Case Target.Column
Case 3
Select Case Target.Row
' Case Range("10:10000")
Case 10, 18, 36, 54, 57, 68, 78, 87, 95, 103, 111, 115, 136, 148, 155, 164, 170, _
173, 179, 208, 216, 283, 293, 301, 307, 323, 333, 338, 353, 370, 376, 395, 423, 433, 462, 475, 494, 523, 533, 542, 560, 569, 584, 593, 603, 620, 638, 675, 692, 700, 705, 724, 727, 731, 735, 739, 743, 747
With Target(1, 1)
.Font.Name = "Wingdings" 'Zelle in Schriftart "Wingdings" formatieren
.Font.Size = 14 'Hier die Gr?sse anpassen !
.Value = IIf(.Value = Chr(168), Chr(254), Chr(168)) 'Wechselt zwischen leerem _
und angehaktem K?stchen
End With
End Select
End Select
'Erstellt in Bereich C12:C248 bei Klick ein leeres K?stchen oder ein angehaktes K? _
stchen
Select Case Target.Column
Case 5
' Select Case Target.Row
' Case 12, 13, 14, 15, 16, 17, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, _
32, 33, 34, 35, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 55, 56, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 69, 70, 71, 72, 73, 74, 75, 76, 77, 79, 80, 81, 82, 83, 84, 85, 86, 88, 89, 90, 91, 92, 93, 94, 96, 97, 98, 99, 100, 101, 102, 104, 105, 106, 107, 108, 109, 110, 112, 113, 114, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 149, 150, 151, 152, 153, 154, 156, 157, 158, 159, 160, 161, 162, 163, 165, 166, 167, 168, 169, 171, 172, 174, 175, 176, 177, 178, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 209, 210, 211, 212, 213, 214, 215, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248
With Target(1, 1)
.Font.Name = "Wingdings" 'Zelle in Schriftart "Wingdings" formatieren
.Font.Size = 14 'Hier die Gr?sse anpassen !
.Value = IIf(.Value = Chr(168), Chr(254), Chr(168)) 'Wechselt zwischen leerem _
und angehaktem K?stchen
End With
' End Select
End Select
'Erstellt in Bereich C249:446 bei Klick ein leeres K?stchen oder ein angehaktes K?stchen
Select Case Target.Column
Case 5
' Select Case Target.Row
' Case 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, _
264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 284, 285, 286, 287, 288, 289, 290, 291, 292, 294, 295, 296, 297, 298, 299, 300, 302, 303, 304, 305, 306, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 324, 325, 326, 327, 328, 329, 330, 331, 332, 334, 335, 336, 337, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 371, 372, 373, 374, 375, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 424, 425, 426, 427, 428, 429, 430, 431, 432, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446
With Target(1, 1)
.Font.Name = "Wingdings" 'Zelle in Schriftart "Wingdings" formatieren
.Font.Size = 14 'Hier die Gr?sse anpassen !
.Value = IIf(.Value = Chr(168), Chr(254), Chr(168)) 'Wechselt zwischen leerem _
und angehaktem K?stchen
End With
' End Select
End Select
'Erstellt in Bereich C504:720 bei Klick ein leeres K?stchen oder ein angehaktes K?stchen
Select Case Target.Column
Case 5
' Select Case Target.Row
'Case 504, 505, 506, 507, 508, 509, 510, 511, 512, 513, 514, 515, 516, 517, 518, 519, 520, 521, _
522, 524, 525, 526, 527, 528, 529, 530, 531, 532, 534, 535, 536, 537, 538, 539, 540, 541, 543, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 561, 562, 563, 564, 565, 566, 567, 568, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581, 582, 583, 585, 586, 587, 588, 589, 590, 591, 592, 594, 595, 596, 597, 598, 599, 600, 601, 602, 604, 605, 606, 607, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630, 631, 632, 633, 634, 635, 636, 637, 639, 640, 641, 642, 643, 644, 645, 646, 647, 648, 649, 650, 651, 652, 653, 654, 655, 656, 657, 658, 659, 660, 661, 662, 663, 664, 665, 666, 667, 668, 669, 670, 671, 672, 673, 674, 676, 677, 678, 679, 680, 681, 682, 683, 684, 685, 686, 687, 688, 689, 690, 691, 693, 694, 695, 696, 697, 698, 699, 701, 702, 703, 704, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719, 720
With Target(1, 1)
.Font.Name = "Wingdings" 'Zelle in Schriftart "Wingdings" formatieren
.Font.Size = 14 'Hier die Gr?sse anpassen !
.Value = IIf(.Value = Chr(168), Chr(254), Chr(168)) 'Wechselt zwischen leerem _
und angehaktem K?stchen
End With
'End Select
End Select
'Erstellt in Bereich C721:760 bei Klick ein leeres K?stchen oder ein angehaktes K? _
stchen
Select Case Target.Column
Case 5
' Select Case Target.Row
'Case 721, 722, 723, 725, 726, 728, 729, 730, 732, 733, 734, 736, 737, 738, 740, 741, 742, 744, _
745, 746, 748, 749, 750, 751, 752, 753, 754, 755, 756, 757, 758, 759, 760
With Target(1, 1)
.Font.Name = "Wingdings" 'Zelle in Schriftart "Wingdings" formatieren
.Font.Size = 14 'Hier die Gr?sse anpassen !
.Value = IIf(.Value = Chr(168), Chr(254), Chr(168)) 'Wechselt zwischen leerem _
und angehaktem K?stchen
End With
End Select
' End Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Range("c10"), "?") Then
Rows("11:17").Hidden = False
Else
Rows("11:17").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C18"), "?") Then
Rows("19:35").Hidden = False
Else
Rows("19:35").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C36"), "?") Then
Rows("37:53").Hidden = False
Else
Rows("37:53").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C54"), "?") Then
Rows("55:56").Hidden = False
Else
Rows("55:56").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C57"), "?") Then
Rows("58:67").Hidden = False
Else
Rows("58:67").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C68"), "?") Then
Rows("69:77").Hidden = False
Else
Rows("69:77").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C78"), "?") Then
Rows("79:86").Hidden = False
Else
Rows("79:86").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C87"), "?") Then
Rows("88:94").Hidden = False
Else
Rows("88:94").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C95"), "?") Then
Rows("96:102").Hidden = False
Else
Rows("96:102").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C103"), "?") Then
Rows("104:110").Hidden = False
Else
Rows("104:100").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C111"), "?") Then
Rows("112:114").Hidden = False
Else
Rows("112:114").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C115"), "?") Then
Rows("116:135").Hidden = False
Else
Rows("116:135").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C136"), "?") Then
Rows("137:147").Hidden = False
Else
Rows("137:147").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C148"), "?") Then
Rows("149:154").Hidden = False
Else
Rows("149:154").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C155"), "?") Then
Rows("156:163").Hidden = False
Else
Rows("156:163").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C164"), "?") Then
Rows("165:169").Hidden = False
Else
Rows("165:169").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C170"), "?") Then
Rows("171:172").Hidden = False
Else
Rows("171:172").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C173"), "?") Then
Rows("174:178").Hidden = False
Else
Rows("174:178").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C179"), "?") Then
Rows("180:207").Hidden = False
Else
Rows("180:207").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C208"), "?") Then
Rows("209:215").Hidden = False
Else
Rows("209:215").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C216"), "?") Then
Rows("217:282").Hidden = False
Else
Rows("217:282").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C283"), "?") Then
Rows("284:294").Hidden = False
Else
Rows("284:294").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C293"), "?") Then
Rows("294:300").Hidden = False
Else
Rows("294:300").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C301"), "?") Then
Rows("302:306").Hidden = False
Else
Rows("302:306").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C307"), "?") Then
Rows("308:322").Hidden = False
Rows(309).Hidden = False
Else
Rows("308:322").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C323"), "?") Then
Rows("324:332").Hidden = False
Else
Rows("324:332").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C333"), "?") Then
Rows("334:337").Hidden = False
Else
Rows("334:337").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C338"), "?") Then
Rows("339:352").Hidden = False
Else
Rows("339:352").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C353"), "?") Then
Rows("354:369").Hidden = False
Else
Rows("354:369").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C370"), "?") Then
Rows("371:375").Hidden = False
Else
Rows("371:375").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C376"), "?") Then
Rows("377:394").Hidden = False
Else
Rows("377:394").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C395"), "?") Then
Rows("396:422").Hidden = False
Else
Rows("396:422").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C423"), "?") Then
Rows("424:432").Hidden = False
Else
Rows("424:432").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C433"), "?") Then
Rows("434:461").Hidden = False
Else
Rows("434:461").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C462"), "?") Then
Rows("463:474").Hidden = False
Else
Rows("463:474").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C475"), "?") Then
Rows("476:493").Hidden = False
Else
Rows("476:493").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C494"), "?") Then
Rows("495:522").Hidden = False
Else
Rows("495:522").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C523"), "?") Then
Rows("524:532").Hidden = False
Else
Rows("524:532").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C533"), "?") Then
Rows("534:541").Hidden = False
Else
Rows("534:541").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C542"), "?") Then
Rows("543:559").Hidden = False
Else
Rows("543:559").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C560"), "?") Then
Rows("561:568").Hidden = False
Else
Rows("561:568").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C569"), "?") Then
Rows("570:583").Hidden = False
Else
Rows("570:583").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C584"), "?") Then
Rows("585:592").Hidden = False
Else
Rows("585:592").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C593"), "?") Then
Rows("594:602").Hidden = False
Else
Rows("594:602").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C603"), "?") Then
Rows("604:619").Hidden = False
Else
Rows("604:619").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C620"), "?") Then
Rows("621:637").Hidden = False
Else
Rows("621:637").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C638"), "?") Then
Rows("639:674").Hidden = False
Else
Rows("639:674").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C675"), "?") Then
Rows("676:691").Hidden = False
Else
Rows("676:691").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C692"), "?") Then
Rows("693:699").Hidden = False
Else
Rows("693:699").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C700"), "?") Then
Rows("701:704").Hidden = False
Else
Rows("701:704").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C705"), "?") Then
Rows("706:723").Hidden = False
Else
Rows("706:723").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C724"), "?") Then
Rows("725:726").Hidden = False
Else
Rows("725:726").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C727"), "?") Then
Rows("728:730").Hidden = False
Else
Rows("728:730").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C731"), "?") Then
Rows("732:734").Hidden = False
Else
Rows("732:734").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C735"), "?") Then
Rows("736:738").Hidden = False
Else
Rows("736:738").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C739"), "?") Then
Rows("740:742").Hidden = False
Else
Rows("740:742").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C743"), "?") Then
Rows("744:746").Hidden = False
Else
Rows("744:746").Hidden = True
End If
If WorksheetFunction.CountIf(Range("C747"), "?") Then
Rows("748:760").Hidden = False
Else
Rows("748:760").Hidden = True
End If
End Sub