Procédure trop Grande
b
Bonjour,
Je viens de terminer d'écrire une procédure pour mon premier onglet Excel. Il s'agit d'une répétition de condition du type :
Si la case AB4 contient la référence "XXXX" alors j'affiche les lignes (X:X) et je masque les lignes (X:X) sinon ........
J'ai 546 références différentes.
Celle ci marchait très bien pour 100 références mais après avoir écrit ma procédure pour les 546 références, Excel me met le message d'erreur suivant : "Procédure trop grande"
Quel est la solution ? Peut on diviser la procédure en plusieurs procédures ? Peut on optimiser le code et comment ?
Voici un bout de mon code :
Sub Worksheet_Change(ByVal Target As Range)
'As As (S) - DEALER - 1
If Range("AB4").Value = "R1R1D" Or Range("AB4").Value = "N1N1D" Then
Dim FR1$, FR2$, FR3$
FR1 = "19:18": FR2 = "19:26": FR3 = "27:4586"
Rows(FR1).Hidden = True
Rows(FR2).Hidden = False
Rows(FR3).Hidden = True
Else
'AS As (D) - DEALER - 2
If Range("AB4").Value = "R1N1D" Or Range("AB4").Value = "N1R1D" Then
Dim FR4$, FR5$, FR6$
FR4 = "19:18": FR5 = "19:26": FR6 = "27:4586"
Rows(FR4).Hidden = True
Rows(FR5).Hidden = False
Rows(FR6).Hidden = True
Else
'AS As (S) - SMALL BLIND - 3
If Range("AB4").Value = "R1R1SB" Or Range("AB4").Value = "N1N1SB" Then
Dim FR7$, FR8$, FR9$
FR7 = "19:27": FR8 = "28:35": FR9 = "36:4586"
Rows(FR7).Hidden = True
Rows(FR8).Hidden = False
Rows(FR9).Hidden = True
Else
'AS As (D) - SMALL BLIND - 4
If Range("AB4").Value = "R1N1SB" Or Range("AB4").Value = "N1R1SB" Then
Dim FR10$, FR11$, FR12$
FR10 = "19:27": FR11 = "28:35": FR12 = "36:4586"
Rows(FR10).Hidden = True
Rows(FR11).Hidden = False
Rows(FR12).Hidden = True
Else
'AS As (S) - BIG BLIND - 5
If Range("AB4").Value = "R1R1BB" Or Range("AB4").Value = "N1N1BB" Then
Dim FR13$, FR14$, FR15$
FR13 = "19:36": FR14 = "37:44": FR15 = "45:4586"
Rows(FR13).Hidden = True
Rows(FR14).Hidden = False
Rows(FR15).Hidden = True
Else
'AS As (D) - BIG BLIND - 6
If Range("AB4").Value = "R1N1BB" Or Range("AB4").Value = "N1R1BB" Then
Dim FR16$, FR17$, FR18$
FR16 = "19:36": FR17 = "37:44": FR18 = "45:4586"
Rows(FR16).Hidden = True
Rows(FR17).Hidden = False
Rows(FR18).Hidden = True
Else
'AS Roi (S) - DEALER - 7
If Range("AB4").Value = "R1RRD" Or Range("AB4").Value = "N1NRD" Then
Dim FR19$, FR20$, FR21$
FR19 = "19:45": FR20 = "46:53": FR21 = "54:4586"
Rows(FR19).Hidden = True
Rows(FR20).Hidden = False
Rows(FR21).Hidden = True
Else
'AS Roi (S) - SMALL BLIND - 8
If Range("AB4").Value = "R1RRSB" Or Range("AB4").Value = "N1NRSB" Then
Dim FR22$, FR23$, FR24$
FR22 = "19:54": FR23 = "55:62": FR24 = "63:4586"
Rows(FR22).Hidden = True
Rows(FR23).Hidden = False
Rows(FR24).Hidden = True
Else
'AS Roi (S) - BIG BLIND - 9
If Range("AB4").Value = "R1RRBB" Or Range("AB4").Value = "N1NRBB" Then
Dim FR25$, FR26$, FR27$
FR25 = "19:63": FR26 = "64:71": FR27 = "72:4586"
Rows(FR25).Hidden = True
Rows(FR26).Hidden = False
Rows(FR27).Hidden = True
Else
'AS Dame (S) - DEALER - 10
If Range("AB4").Value = "R1RDD" Or Range("AB4").Value = "N1NDD" Then
Dim FR28$, FR29$, FR30$
FR28 = "19:72": FR29 = "73:80": FR30 = "81:4586"
Rows(FR28).Hidden = True
Rows(FR29).Hidden = False
Rows(FR30).Hidden = True
Else
'AS Dame (S) - SMALL BLIND - 11
If Range("AB4").Value = "R1RDSB" Or Range("AB4").Value = "N1NDSB" Then
Dim FR31$, FR32$, FR33$
FR31 = "19:81": FR32 = "82:89": FR33 = "90:4586"
Rows(FR31).Hidden = True
Rows(FR32).Hidden = False
Rows(FR33).Hidden = True
Else
'AS Dame (S) - BIG BLIND - 12
If Range("AB4").Value = "R1RDBB" Or Range("AB4").Value = "N1NDBB" Then
Dim FR34$, FR35$, FR36$
FR34 = "19:90": FR35 = "91:98": FR36 = "99:4586"
Rows(FR34).Hidden = True
Rows(FR35).Hidden = False
Rows(FR36).Hidden = True
Else
'AS Valet (S) - DEALER - 13
If Range("AB4").Value = "R1RJD" Or Range("AB4").Value = "N1NJD" Then
Dim FR37$, FR38$, FR39$
FR37 = "19:99": FR38 = "100:107": FR39 = "108:4586"
Rows(FR37).Hidden = True
Rows(FR38).Hidden = False
Rows(FR39).Hidden = True
Else
'AS Valet (S) - SMALL BLIND - 14
If Range("AB4").Value = "R1RJSB" Or Range("AB4").Value = "N1NJSB" Then
Dim FR40$, FR41$, FR42$
FR40 = "19:108": FR41 = "109:116": FR42 = "117:4586"
Rows(FR40).Hidden = True
Rows(FR41).Hidden = False
Rows(FR42).Hidden = True
Else
'AS Valet (S) - BIG BLIND - 15
If Range("AB4").Value = "R1RJBB" Or Range("AB4").Value = "N1NJBB" Then
Dim FR43$, FR44$, FR45$
FR43 = "19:117": FR44 = "118:125": FR45 = "126:4586"
Rows(FR43).Hidden = True
Rows(FR44).Hidden = False
Rows(FR45).Hidden = True
Else
'AS 10 (S) - DEALER - 16
If Range("AB4").Value = "R1R10D" Or Range("AB4").Value = "N1N10D" Then
Dim FR46$, FR47$, FR48$
FR46 = "19:126": FR47 = "127:134": FR48 = "135:4586"
Rows(FR46).Hidden = True
Rows(FR47).Hidden = False
Rows(FR48).Hidden = True
Else
'AS 10 (S) - SMALL BLIND - 17
If Range("AB4").Value = "R1R10SB" Or Range("AB4").Value = "N1N10SB" Then
Dim FR49$, FR50$, FR51$
FR49 = "19:135": FR50 = "136:143": FR51 = "144:4586"
Rows(FR49).Hidden = True
Rows(FR50).Hidden = False
Rows(FR51).Hidden = True
Else
'AS 10 (S) - BIG BLIND - 18
If Range("AB4").Value = "R1R10BB" Or Range("AB4").Value = "N1N10BB" Then
Dim FR52$, FR53$, FR54$
FR52 = "19:144": FR53 = "145:152": FR54 = "153:4586"
Rows(FR52).Hidden = True
Rows(FR53).Hidden = False
Rows(FR54).Hidden = True
Else
'AS 9 (S) - DEALER - 19
If Range("AB4").Value = "R1R9D" Or Range("AB4").Value = "N1N9D" Then
Dim FR55$, FR56$, FR57$
FR55 = "19:153": FR56 = "154:161": FR57 = "162:4586"
Rows(FR55).Hidden = True
Rows(FR56).Hidden = False
Rows(FR57).Hidden = True
Else
'AS 9 (S) - SMALL BLIND - 20
If Range("AB4").Value = "R1R9SB" Or Range("AB4").Value = "N1N9SB" Then
Dim FR58$, FR59$, FR60$
FR58 = "19:162": FR59 = "163:170": FR60 = "171:4586"
Rows(FR58).Hidden = True
Rows(FR59).Hidden = False
Rows(FR60).Hidden = True
Else
'AS 9 (S) - BIG BLIND - 21
If Range("AB4").Value = "R1R9BB" Or Range("AB4").Value = "N1N9BB" Then
Dim FR61$, FR62$, FR63$
FR61 = "19:171": FR62 = "172:179": FR63 = "180:4586"
Rows(FR61).Hidden = True
Rows(FR62).Hidden = False
Rows(FR63).Hidden = True
Else
'AS 8 (S) - DEALER - 22
If Range("AB4").Value = "R1R8D" Or Range("AB4").Value = "N1N8D" Then
Dim FR64$, FR65$, FR66$
FR64 = "19:180": FR65 = "181:188": FR66 = "189:4586"
Rows(FR64).Hidden = True
Rows(FR65).Hidden = False
Rows(FR66).Hidden = True
Else
'AS 8 (S) - SMALL BLIND - 23
If Range("AB4").Value = "R1R8SB" Or Range("AB4").Value = "N1N8SB" Then
Dim FR67$, FR68$, FR69$
FR67 = "19:189": FR68 = "190:197": FR69 = "198:4586"
Rows(FR67).Hidden = True
Rows(FR68).Hidden = False
Rows(FR69).Hidden = True
Else
'AS 8 (S) - BIG BLIND - 24
If Range("AB4").Value = "R1R8BB" Or Range("AB4").Value = "N1N8BB" Then
Dim FR70$, FR71$, FR72$
FR70 = "19:198": FR71 = "199:206": FR72 = "207:4586"
Rows(FR70).Hidden = True
Rows(FR71).Hidden = False
Rows(FR72).Hidden = True
Else
'AS 7 (S) - DEALER - 25
If Range("AB4").Value = "R1R7D" Or Range("AB4").Value = "N1N7D" Then
Dim FR73$, FR74$, FR75$
FR73 = "19:207": FR74 = "208:215": FR75 = "216:4586"
Rows(FR73).Hidden = True
Rows(FR74).Hidden = False
Rows(FR75).Hidden = True
Else
'AS 7 (S) - SMALL BLIND - 26
If Range("AB4").Value = "R1R7SB" Or Range("AB4").Value = "N1N7SB" Then
Dim FR76$, FR77$, FR78$
FR76 = "19:216": FR77 = "217:224": FR78 = "225:4586"
Rows(FR76).Hidden = True
Rows(FR77).Hidden = False
Rows(FR78).Hidden = True
Else
'AS 7 (S) - BIG BLIND - 27
If Range("AB4").Value = "R1R7BB" Or Range("AB4").Value = "N1N7BB" Then
Dim FR79$, FR80$, FR81$
FR79 = "19:225": FR80 = "226:233": FR81 = "234:4586"
Rows(FR79).Hidden = True
Rows(FR80).Hidden = False
Rows(FR81).Hidden = True
Else
'AS 6 (S) - DEALER - 28
If Range("AB4").Value = "R1R6D" Or Range("AB4").Value = "N1N6D" Then
Dim FR82$, FR83$, FR84$
FR82 = "19:234": FR83 = "235:242": FR84 = "243:4586"
Rows(FR82).Hidden = True
Rows(FR83).Hidden = False
Rows(FR84).Hidden = True
Else
'AS 6 (S) - SMALL BLIND - 29
If Range("AB4").Value = "R1R6SB" Or Range("AB4").Value = "N1N6SB" Then
Dim FR85$, FR86$, FR87$
FR85 = "19:243": FR86 = "244:251": FR87 = "252:4586"
Rows(FR85).Hidden = True
Rows(FR86).Hidden = False
Rows(FR87).Hidden = True
Else
'AS 6 (S) - BIG BLIND - 30
If Range("AB4").Value = "R1R6BB" Or Range("AB4").Value = "N1N6BB" Then
Dim FR88$, FR89$, FR90$
FR88 = "19:252": FR89 = "253:260": FR90 = "261:4586"
Rows(FR88).Hidden = True
Rows(FR89).Hidden = False
Rows(FR90).Hidden = True
Else
'AS 5 (S) - DEALER - 31
If Range("AB4").Value = "R1R5D" Or Range("AB4").Value = "N1N5D" Then
Dim FR91$, FR92$, FR93$
FR91 = "19:261": FR92 = "262:269": FR93 = "270:4586"
Rows(FR91).Hidden = True
Rows(FR92).Hidden = False
Rows(FR93).Hidden = True
Else
'AS 5 (S) - SMALL BLIND - 32
If Range("AB4").Value = "R1R5SB" Or Range("AB4").Value = "N1N5SB" Then
Dim FR94$, FR95$, FR96$
FR94 = "19:270": FR95 = "271:278": FR96 = "279:4586"
Rows(FR94).Hidden = True
Rows(FR95).Hidden = False
Rows(FR96).Hidden = True
Else
'AS 5 (S) - BIG BLIND - 33
If Range("AB4").Value = "R1R5BB" Or Range("AB4").Value = "N1N5BB" Then
Dim FR97$, FR98$, FR99$
FR97 = "19:279": FR98 = "280:287": FR99 = "288:4586"
Rows(FR97).Hidden = True
Rows(FR98).Hidden = False
Rows(FR99).Hidden = True
Else
'AS 4 (S) - DEALER - 34
If Range("AB4").Value = "R1R4D" Or Range("AB4").Value = "N1N4D" Then
Dim FR100$, FR101$, FR102$
FR100 = "19:288": FR101 = "289:296": FR102 = "297:4586"
Rows(FR100).Hidden = True
Rows(FR101).Hidden = False
Rows(FR102).Hidden = True
Else
'AS 4 (S) - SMALL BLIND - 35
If Range("AB4").Value = "R1R4SB" Or Range("AB4").Value = "N1N4SB" Then
Dim FR103$, FR104$, FR105$
FR103 = "19:297": FR104 = "298:305": FR105 = "306:4586"
Rows(FR103).Hidden = True
Rows(FR104).Hidden = False
Rows(FR105).Hidden = True
Else
'AS 4 (S) - BIG BLIND - 36
If Range("AB4").Value = "R1R4BB" Or Range("AB4").Value = "N1N4BB" Then
Dim FR106$, FR107$, FR108$
FR106 = "19:306": FR107 = "307:314": FR108 = "315:4586"
Rows(FR106).Hidden = True
Rows(FR107).Hidden = False
Rows(FR108).Hidden = True
Else
'AS 3 (S) - DEALER - 37
If Range("AB4").Value = "R1R3D" Or Range("AB4").Value = "N1N3D" Then
Dim FR109$, FR110$, FR111$
FR109 = "19:315": FR110 = "316:323": FR111 = "324:4586"
Rows(FR109).Hidden = True
Rows(FR110).Hidden = False
Rows(FR111).Hidden = True
Else
'AS 3 (S) - SMALL BLIND - 38
If Range("AB4").Value = "R1R3SB" Or Range("AB4").Value = "N1N3SB" Then
Dim FR112$, FR113$, FR114$
FR112 = "19:324": FR113 = "325:332": FR114 = "333:4586"
Rows(FR112).Hidden = True
Rows(FR113).Hidden = False
Rows(FR114).Hidden = True
Else
'AS 3 (S) - BIG BLIND - 39
If Range("AB4").Value = "R1R3BB" Or Range("AB4").Value = "N1N3BB" Then
Dim FR115$, FR116$, FR117$
FR115 = "19:333": FR116 = "334:341": FR117 = "342:4586"
Rows(FR115).Hidden = True
Rows(FR116).Hidden = False
Rows(FR117).Hidden = True
Else
'AS 2 (S) - DEALER - 40
If Range("AB4").Value = "R1R2D" Or Range("AB4").Value = "N1N2D" Then
Dim FR118$, FR119$, FR120$
FR118 = "19:342": FR119 = "343:350": FR120 = "351:4586"
Rows(FR118).Hidden = True
Rows(FR119).Hidden = False
Rows(FR120).Hidden = True
Else
'AS 2 (S) - SMALL BLIND - 41
If Range("AB4").Value = "R1R2SB" Or Range("AB4").Value = "N1N2SB" Then
Dim FR121$, FR122$, FR123$
FR121 = "19:351": FR122 = "352:359": FR123 = "360:4586"
Rows(FR121).Hidden = True
Rows(FR122).Hidden = False
Rows(FR123).Hidden = True
Else
'AS 2 (S) - BIG BLIND - 42
If Range("AB4").Value = "R1R2BB" Or Range("AB4").Value = "N1N2BB" Then
Dim FR124$, FR125$, FR126$
FR124 = "19:360": FR125 = "361:368": FR126 = "369:4586"
Rows(FR124).Hidden = True
Rows(FR125).Hidden = False
Rows(FR126).Hidden = True]
Bonjour,
Ta demande aurait dû être dans la section Excel - VBA.
Dim FR1$, FR2$, FR3$ auraient pu être placés au haut et être utilisés dans tous les cas.
Nul besoin de les incrémenter.
Un essai ...
Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("AB4").Value
Case "R1R1D", "N1N1D"
Dim FR1$, FR2$, FR3$
FR1 = "19:18": FR2 = "19:26": FR3 = "27:4586"
Rows(FR1).Hidden = True
Rows(FR2).Hidden = False
Rows(FR3).Hidden = True
Case "R1N1D", "N1R1D"
Dim FR4$, FR5$, FR6$
FR4 = "19:18": FR5 = "19:26": FR6 = "27:4586"
Rows(FR4).Hidden = True
Rows(FR5).Hidden = False
Rows(FR6).Hidden = True
Case "R1R1SB", "N1N1SB"
Dim FR7$, FR8$, FR9$
FR7 = "19:27": FR8 = "28:35": FR9 = "36:4586"
Rows(FR7).Hidden = True
Rows(FR8).Hidden = False
Rows(FR9).Hidden = True
Case "R1N1SB", "N1R1SB"
Dim FR10$, FR11$, FR12$
FR10 = "19:27": FR11 = "28:35": FR12 = "36:4586"
Rows(FR10).Hidden = True
Rows(FR11).Hidden = False
Rows(FR12).Hidden = True
Case "R1R1BB", "N1N1BB"
Dim FR13$, FR14$, FR15$
FR13 = "19:36": FR14 = "37:44": FR15 = "45:4586"
Rows(FR13).Hidden = True
Rows(FR14).Hidden = False
Rows(FR15).Hidden = True
Case "R1N1BB", "N1R1BB"
Dim FR16$, FR17$, FR18$
FR16 = "19:36": FR17 = "37:44": FR18 = "45:4586"
Rows(FR16).Hidden = True
Rows(FR17).Hidden = False
Rows(FR18).Hidden = True
Case "R1RRD", "N1NRD"
Dim FR19$, FR20$, FR21$
FR19 = "19:45": FR20 = "46:53": FR21 = "54:4586"
Rows(FR19).Hidden = True
Rows(FR20).Hidden = False
Rows(FR21).Hidden = True
Case "R1RRSB", "N1NRSB"
Dim FR22$, FR23$, FR24$
FR22 = "19:54": FR23 = "55:62": FR24 = "63:4586"
Rows(FR22).Hidden = True
Rows(FR23).Hidden = False
Rows(FR24).Hidden = True
Case "R1RRBB", "N1NRBB"
Dim FR25$, FR26$, FR27$
FR25 = "19:63": FR26 = "64:71": FR27 = "72:4586"
Rows(FR25).Hidden = True
Rows(FR26).Hidden = False
Rows(FR27).Hidden = True
Case "R1RDD", "N1NDD"
Dim FR28$, FR29$, FR30$
FR28 = "19:72": FR29 = "73:80": FR30 = "81:4586"
Rows(FR28).Hidden = True
Rows(FR29).Hidden = False
Rows(FR30).Hidden = True
Case "R1RDSB", "N1NDSB"
Dim FR31$, FR32$, FR33$
FR31 = "19:81": FR32 = "82:89": FR33 = "90:4586"
Rows(FR31).Hidden = True
Rows(FR32).Hidden = False
Rows(FR33).Hidden = True
Case "R1RDBB", "N1NDBB"
Dim FR34$, FR35$, FR36$
FR34 = "19:90": FR35 = "91:98": FR36 = "99:4586"
Rows(FR34).Hidden = True
Rows(FR35).Hidden = False
Rows(FR36).Hidden = True
Case "R1RJD", "N1NJD"
Dim FR37$, FR38$, FR39$
FR37 = "19:99": FR38 = "100:107": FR39 = "108:4586"
Rows(FR37).Hidden = True
Rows(FR38).Hidden = False
Rows(FR39).Hidden = True
Case "R1RJSB", "N1NJSB"
Dim FR40$, FR41$, FR42$
FR40 = "19:108": FR41 = "109:116": FR42 = "117:4586"
Rows(FR40).Hidden = True
Rows(FR41).Hidden = False
Rows(FR42).Hidden = True
Case "R1RJBB", "N1NJBB"
Dim FR43$, FR44$, FR45$
FR43 = "19:117": FR44 = "118:125": FR45 = "126:4586"
Rows(FR43).Hidden = True
Rows(FR44).Hidden = False
Rows(FR45).Hidden = True
Case "R1R10D", "N1N10D"
Dim FR46$, FR47$, FR48$
FR46 = "19:126": FR47 = "127:134": FR48 = "135:4586"
Rows(FR46).Hidden = True
Rows(FR47).Hidden = False
Rows(FR48).Hidden = True
Case "R1R10SB", "N1N10SB"
Dim FR49$, FR50$, FR51$
FR49 = "19:135": FR50 = "136:143": FR51 = "144:4586"
Rows(FR49).Hidden = True
Rows(FR50).Hidden = False
Rows(FR51).Hidden = True
Case "R1R10BB", "N1N10BB"
Dim FR52$, FR53$, FR54$
FR52 = "19:144": FR53 = "145:152": FR54 = "153:4586"
Rows(FR52).Hidden = True
Rows(FR53).Hidden = False
Rows(FR54).Hidden = True
Case "R1R9D", "N1N9D"
Dim FR55$, FR56$, FR57$
FR55 = "19:153": FR56 = "154:161": FR57 = "162:4586"
Rows(FR55).Hidden = True
Rows(FR56).Hidden = False
Rows(FR57).Hidden = True
Case "R1R9SB", "N1N9SB"
Dim FR58$, FR59$, FR60$
FR58 = "19:162": FR59 = "163:170": FR60 = "171:4586"
Rows(FR58).Hidden = True
Rows(FR59).Hidden = False
Rows(FR60).Hidden = True
Case "R1R9BB", "N1N9BB"
Dim FR61$, FR62$, FR63$
FR61 = "19:171": FR62 = "172:179": FR63 = "180:4586"
Rows(FR61).Hidden = True
Rows(FR62).Hidden = False
Rows(FR63).Hidden = True
Case "R1R8D", "N1N8D"
Dim FR64$, FR65$, FR66$
FR64 = "19:180": FR65 = "181:188": FR66 = "189:4586"
Rows(FR64).Hidden = True
Rows(FR65).Hidden = False
Rows(FR66).Hidden = True
Case "R1R8SB", "N1N8SB"
Dim FR67$, FR68$, FR69$
FR67 = "19:189": FR68 = "190:197": FR69 = "198:4586"
Rows(FR67).Hidden = True
Rows(FR68).Hidden = False
Rows(FR69).Hidden = True
Case "R1R8BB", "N1N8BB"
Dim FR70$, FR71$, FR72$
FR70 = "19:198": FR71 = "199:206": FR72 = "207:4586"
Rows(FR70).Hidden = True
Rows(FR71).Hidden = False
Rows(FR72).Hidden = True
Case "R1R7D", "N1N7D"
Dim FR73$, FR74$, FR75$
FR73 = "19:207": FR74 = "208:215": FR75 = "216:4586"
Rows(FR73).Hidden = True
Rows(FR74).Hidden = False
Rows(FR75).Hidden = True
Case "R1R7SB", "N1N7SB"
Dim FR76$, FR77$, FR78$
FR76 = "19:216": FR77 = "217:224": FR78 = "225:4586"
Rows(FR76).Hidden = True
Rows(FR77).Hidden = False
Rows(FR78).Hidden = True
Case "R1R7BB", "N1N7BB"
Dim FR79$, FR80$, FR81$
FR79 = "19:225": FR80 = "226:233": FR81 = "234:4586"
Rows(FR79).Hidden = True
Rows(FR80).Hidden = False
Rows(FR81).Hidden = True
Case "R1R6D", "N1N6D"
Dim FR82$, FR83$, FR84$
FR82 = "19:234": FR83 = "235:242": FR84 = "243:4586"
Rows(FR82).Hidden = True
Rows(FR83).Hidden = False
Rows(FR84).Hidden = True
Case "R1R6SB", "N1N6SB"
Dim FR85$, FR86$, FR87$
FR85 = "19:243": FR86 = "244:251": FR87 = "252:4586"
Rows(FR85).Hidden = True
Rows(FR86).Hidden = False
Rows(FR87).Hidden = True
Case "R1R6BB", "N1N6BB"
Dim FR88$, FR89$, FR90$
FR88 = "19:252": FR89 = "253:260": FR90 = "261:4586"
Rows(FR88).Hidden = True
Rows(FR89).Hidden = False
Rows(FR90).Hidden = True
Case "R1R5D", "N1N5D"
Dim FR91$, FR92$, FR93$
FR91 = "19:261": FR92 = "262:269": FR93 = "270:4586"
Rows(FR91).Hidden = True
Rows(FR92).Hidden = False
Rows(FR93).Hidden = True
Case "R1R5SB", "N1N5SB"
Dim FR94$, FR95$, FR96$
FR94 = "19:270": FR95 = "271:278": FR96 = "279:4586"
Rows(FR94).Hidden = True
Rows(FR95).Hidden = False
Rows(FR96).Hidden = True
Case "R1R5BB", "N1N5BB"
Dim FR97$, FR98$, FR99$
FR97 = "19:279": FR98 = "280:287": FR99 = "288:4586"
Rows(FR97).Hidden = True
Rows(FR98).Hidden = False
Rows(FR99).Hidden = True
Case "R1R4D", "N1N4D"
Dim FR100$, FR101$, FR102$
FR100 = "19:288": FR101 = "289:296": FR102 = "297:4586"
Rows(FR100).Hidden = True
Rows(FR101).Hidden = False
Rows(FR102).Hidden = True
Case "R1R4SB", "N1N4SB"
Dim FR103$, FR104$, FR105$
FR103 = "19:297": FR104 = "298:305": FR105 = "306:4586"
Rows(FR103).Hidden = True
Rows(FR104).Hidden = False
Rows(FR105).Hidden = True
Case "R1R4BB", "N1N4BB"
Dim FR106$, FR107$, FR108$
FR106 = "19:306": FR107 = "307:314": FR108 = "315:4586"
Rows(FR106).Hidden = True
Rows(FR107).Hidden = False
Rows(FR108).Hidden = True
Case "R1R3D", "N1N3D"
Dim FR109$, FR110$, FR111$
FR109 = "19:315": FR110 = "316:323": FR111 = "324:4586"
Rows(FR109).Hidden = True
Rows(FR110).Hidden = False
Rows(FR111).Hidden = True
Case "R1R3SB", "N1N3SB"
Dim FR112$, FR113$, FR114$
FR112 = "19:324": FR113 = "325:332": FR114 = "333:4586"
Rows(FR112).Hidden = True
Rows(FR113).Hidden = False
Rows(FR114).Hidden = True
Case "R1R3BB", "N1N3BB"
Dim FR115$, FR116$, FR117$
FR115 = "19:333": FR116 = "334:341": FR117 = "342:4586"
Rows(FR115).Hidden = True
Rows(FR116).Hidden = False
Rows(FR117).Hidden = True
Case "R1R2D", "N1N2D"
Dim FR118$, FR119$, FR120$
FR118 = "19:342": FR119 = "343:350": FR120 = "351:4586"
Rows(FR118).Hidden = True
Rows(FR119).Hidden = False
Rows(FR120).Hidden = True
Case "R1R2SB", "N1N2SB"
Dim FR121$, FR122$, FR123$
FR121 = "19:351": FR122 = "352:359": FR123 = "360:4586"
Rows(FR121).Hidden = True
Rows(FR122).Hidden = False
Rows(FR123).Hidden = True
Case "R1R2BB", "N1N2BB"
Dim FR124$, FR125$, FR126$
FR124 = "19:360": FR125 = "361:368": FR126 = "369:4586"
Rows(FR124).Hidden = True
Rows(FR125).Hidden = False
Rows(FR126).Hidden = True
End Select
End Subric
b
Hello Ric
Desole pour l'erreur de topic, je suis nouveau sur le site
Super ca fonctionne.
Pour utiliser partout Dim FR1$, FR2$, FR3$, que dois je modifier ?
Merci pour tout
Bonjour,
Une suggestion ...
Dim FR1$, FR2$, FR3$
Select Case Range("AB4").Value
Case "R1R1D", "N1N1D"
FR1 = "19:18": FR2 = "19:26": FR3 = "27:4586"
Rows(FR1).Hidden = True
Rows(FR2).Hidden = False
Rows(FR3).Hidden = True
Case "R1N1D", "N1R1D"
FR1 = "19:18": FR2 = "19:26": FR3 = "27:4586"
Rows(FR1).Hidden = True
Rows(FR2).Hidden = False
Rows(FR3).Hidden = True
Case "R1R1SB", "N1N1SB"
FR1 = "19:27": FR2 = "28:35": FR3 = "36:4586"
Rows(FR1).Hidden = True
Rows(FR2).Hidden = False
Rows(FR3).Hidden = TrueFaire la même chose pour le reste.
ric
Bonjour à tous,
je te propose de mettre tes paramètres sur une feuille.
Beaucoup plus simple à maintenir en cas d'évolution, et la macro devient simplement :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Address = "$AB$4" Then
With Sheets("Params")
Set c = .[A:D].Find(Target.Value, , xlValues, xlWhole)
[19:4586].EntireRow.Hidden = True
If Not c Is Nothing Then Range(.Cells(c.Row, "E").Value).EntireRow.Hidden = False
End With
End If
End Suberic
b
Hello,
Ou dois je mettre cette procedure et je fais quoi du reste de mon code ?
Merci
Bonjour à tous,
Tu remplaces tout ton code de la macro par celui d'eriiic.
Tu ajoutes la feuille "Params" et son petit tableau.
Tu testes.
Pour les tests, il est préférable de travailler sur une copie.
ric
b
Hello
J'ai fait un copier coller de l'onglet params dans mon fichier et j'ai copier coller la procedure d'Erric dans la feuille contenant ma reference dans ma cellule AB4 mais rien ne se passe.
Puis je vous envoyer mon fichier ?
Bonjour à tousse,
Bien sûr, tu peux envoyer ton fichier.
ric
Bonjour à tousse,
Si ton fichier est trop lourd, tu peux utiliser https://mon-partage.fr/ et donner le lien qui sera créé.
ric
b
Hello
Mon fichier est trop lourd et je ne peux pas partager le lien car nouveau sur le site (je dois échanger au moins 10 message
Puis je te l'envoyer par mail?
b
C'est bon avec une petite astuce
C'est bon pour toi?
Bonjour,
Dans la feuille "Table 3 joueurs", tu modifies les cellules V, X et Z et non AB4.
Modifie la macro ainsi; chacun des 4 choix où modifier déclencheront la macro.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Address = "$AB$4" Or Target.Address = "$V$4" Or Target.Address = "$X$4" Or Target.Address = "$Z$4" Then
With Sheets("Params")
Set c = .[A:D].Find(Target.Value, , xlValues, xlWhole)
[19:4586].EntireRow.Hidden = True
If Not c Is Nothing Then Range(.Cells(c.Row, "E").Value).EntireRow.Hidden = False
End With
End If
End Subric
b
Hello,
Merci pour ton retour.
La macro fonctionne.
Par contre, toutes mes lignes se masquent et les lignes qui doivent s'afficher n'apparaissent pas
Bonjour,
C'est la macro d'eriiic. Je n'ai pas testé si elle correspondait en tout point à ton besoin.
Si eriiic ne repasse pas par ici, je peux regarder cela demain.
ric
Bonjour,
FR1 = "19:18": FR2 = "19:26": FR3 = "27:4586"
Rows(FR1).Hidden = True
Rows(FR2).Hidden = False
Rows(FR3).Hidden = True
Dans la macro fourni dans ta demande, les 2 premiers items, FR1 affiche de 19 à 18, ce n'est pas logique.
Ensuite, FR2 masque 19 à 26, ce qui contredit FR1.
Est-ce correcte ou est-ce une erreur de frappe ????
ric
b
Il s'agit d'une erreur de frappe
Voila le bon code :
FR1 = "18:18": FR2 = "19:26": FR3 = "27:4586"
Rows(FR1).Hidden = True
Rows(FR2).Hidden = False
Rows(FR3).Hidden = True
Bonjour,
Tous les autres commencent-il par 18 aussi ??
Si le code d'eriiic ne fonctionne pas correctement, c'est simplement qu'il a saisi quelques références et te laissait l'opportunité de compléter le tableau.
ric
Ah ben oui, il faut compléter la feuille Params