Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre colonne

rendan2 · 4 Juin 2014

Bonjour,

Je souhaiterai faire une macro qui masquerai automatiquement les lignes pour les cellules contenant un « 0 » en colonne AB (surlignée en rouge).
En enregistrant une macro j’ai bien tenté de sélectionner manuellement les lignes respectant cette condition, mais le résultat est très aléatoire (bugs dans l’exécution de la macro, ligne non masquées, etc).
Entre les SmallScroll Down, Union(Range( _ etc et certaines étapes inutiles qui semblent être automatiquement enregistrées, pour peux que le résultat fonctionne, il faudrait potentiellement que je refasse à chaque fois la sélection manuelle si le contenu en colonne AB change.

J’imagine qu’il faut intégrer une condition dans la macro, mais ne suis pas du tout un expert (en dehors d’enregistrer une macro, puis de voir le code en y apportant parfois de légères améliorations).

Vous trouverez le fichier en pièce jointe un fichier illustrant ma requête, et ci-dessous le code enregistré automatiquement par l’éditeur de macro:
Merci d’avance pour votre aide précieuse.

Sub BU()
'
' BU Macro
'

'
ActiveWindow.SmallScroll Down:=-9
Range("8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23").Select
Range("A23").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46" _
).Select
Range("A46").Activate
ActiveWindow.SmallScroll Down:=18
Range( _
"8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69" _
).Select
Range("A69").Activate
ActiveWindow.SmallScroll Down:=24
Union(Range( _
"8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A92").Activate
ActiveWindow.SmallScroll Down:=21
Union(Range( _
"100:100,108:108,109:109,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89" _
), Range("90:90,91:91,92:92")).Select
Range("A109").Activate
ActiveWindow.SmallScroll Down:=6
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69" _
), Range("77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92")).Select
Range("A115").Activate
ActiveWindow.SmallScroll Down:=21
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46" _
), Range( _
"54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A138").Activate
ActiveWindow.SmallScroll Down:=24
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23" _
), Range( _
"31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A161").Activate
ActiveWindow.SmallScroll Down:=21
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,8:8,16:16,17:17,19:19,20:20" _
), Range( _
"21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A178").Activate
ActiveWindow.SmallScroll Down:=3
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,180:180,181:181,182:182,183:183,184:184" _
), Range( _
"8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A184").Activate
ActiveWindow.SmallScroll Down:=21
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,180:180,181:181,182:182,183:183,184:184" _
), Range( _
"192:192,200:200,201:201,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89" _
), Range("90:90,91:91,92:92")).Select
Range("A201").Activate
ActiveWindow.SmallScroll Down:=3
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,180:180,181:181,182:182,183:183,184:184" _
), Range( _
"192:192,200:200,201:201,203:203,204:204,205:205,206:206,207:207,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69" _
), Range("77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92")).Select
Range("A207").Activate
ActiveWindow.SmallScroll Down:=21
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,180:180,181:181,182:182,183:183,184:184" _
), Range( _
"192:192,200:200,201:201,203:203,204:204,205:205,206:206,207:207,215:215,223:223,224:224,226:226,227:227,228:228,229:229,230:230,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46" _
), Range( _
"54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A230").Activate
ActiveWindow.SmallScroll Down:=21
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,180:180,181:181,182:182,183:183,184:184" _
), Range( _
"192:192,200:200,201:201,203:203,204:204,205:205,206:206,207:207,215:215,223:223,224:224,226:226,227:227,228:228,229:229,230:230,238:238,246:246,247:247,249:249,250:250,251:251,252:252,253:253,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23" _
), Range( _
"31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A253").Activate
ActiveWindow.SmallScroll Down:=21
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,180:180,181:181,182:182,183:183,184:184" _
), Range( _
"192:192,200:200,201:201,203:203,204:204,205:205,206:206,207:207,215:215,223:223,224:224,226:226,227:227,228:228,229:229,230:230,238:238,246:246,247:247,249:249,250:250,251:251,252:252,253:253,261:261,269:269,270:270,272:272,273:273,274:274,275:275,276:276" _
), Range( _
"8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69,77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92" _
)).Select
Range("A276").Activate
ActiveWindow.SmallScroll Down:=24
Union(Range( _
"100:100,108:108,109:109,111:111,112:112,113:113,114:114,115:115,123:123,131:131,132:132,134:134,135:135,136:136,137:137,138:138,146:146,154:154,155:155,157:157,158:158,159:159,160:160,161:161,169:169,177:177,178:178,180:180,181:181,182:182,183:183,184:184" _
), Range( _
"192:192,200:200,201:201,203:203,204:204,205:205,206:206,207:207,215:215,223:223,224:224,226:226,227:227,228:228,229:229,230:230,238:238,246:246,247:247,249:249,250:250,251:251,252:252,253:253,261:261,269:269,270:270,272:272,273:273,274:274,275:275,276:276" _
), Range( _
"284:284,292:292,293:293,295:295,296:296,297:297,298:298,299:299,8:8,16:16,17:17,19:19,20:20,21:21,22:22,23:23,31:31,39:39,40:40,42:42,43:43,44:44,45:45,46:46,54:54,62:62,63:63,65:65,66:66,67:67,68:68,69:69" _
), Range("77:77,85:85,86:86,88:88,89:89,90:90,91:91,92:92")).Select
Range("A299").Activate
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 263
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 257
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 220
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 180
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
Selection.EntireRow.Hidden = True
End Sub

Efgé · 4 Juin 2014

Re : Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre col

Bonjour rendan2

Une proposition:

VB:

Sub test()
Dim i As Long
Application.ScreenUpdating = False
With Sheets("Feuil1") 'Feuille a adapter
    'On démasque toutes les lignes
    .Rows.Hidden = False
    'Pour chaque ligne i de 8 à la dernière remplie en colonne N (14)
    For i = 8 To .Cells(.Rows.Count, 14).End(3).Row
        'La ligne i est masquée si la valeur de
        'la cellule ligne i colonne 28 (AB) est égale à 0
        .Rows(i).Hidden = .Cells(i, 28) = 0
    'Prochaine ligne
    Next i
End With
Application.ScreenUpdating = True
End Sub

Cordialement

rendan2 · 4 Juin 2014

Re : Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre col

Bonjour Efgé,

Tout d'abord merci d'avoir pris le temps de regarder mon problème.
Cela marche très bien, par contre y aurait-il moyen de faire en sorte que ton code ne masque pas les lignes vides certes mais qui ne sont pas égales à 0 (ex : ligne 28 ou 51)?
J'imagine qu'il est possible de différencier le 0 du null dans ton code.

Enfin, je vais probablement abuser de tes connaissances, mais peut-on ajouter une seconde Macro qui permettrait de démasquer les lignes initialement masquées quand on le souhaite ?

Encore merci pour ton aide.

Jacou · 4 Juin 2014

Re : Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre col

Bonjour rendan, bonjour le forum,

voir la macro Worksheet_Change dans le fichier joint

bonne journée

rendan2 · 4 Juin 2014

Re : Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre col

Bonjour Jacou,

Merci pour ton aide.
Cependant, j'ai ouvert la pièce jointe 'Macro Rendan" et je ne vois aucune macro associée.
Est-elle associée à ce classeur?

Efgé · 4 Juin 2014

Re : Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre col

Re
Bonjour Jacou
Deux macros:

VB:

Sub Masque()
Dim i As Long
Application.ScreenUpdating = False
With Sheets("Feuil1") 'Feuille a adapter
   'On démasque toutes les lignes
   .Rows.Hidden = False
    'Pour chaque ligne i de 8 à la dernière remplie en colonne N (14)
   For i = 8 To .Cells(.Rows.Count, 14).End(3).Row
        'La ligne i est masquée si la valeur de
       'la cellule ligne i colonne 28 (AB) est égale à 0 et est différente de rien ("")
       If .Cells(i, 28).Value = 0 And .Cells(i, 28) <> "" Then .Rows(i).Hidden = True
    'Prochaine ligne
   Next i
End With
Application.ScreenUpdating = True
End Sub

Et

VB:

Sub Affiche()
Sheets("Feuil1").Rows.Hidden = False
End Sub

Cordialement

rendan2 · 4 Juin 2014

Re : Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre col

Merci beaucoup à vous 2, les macros (afficher / masquer) fonctionnent parfaitement!

Bonne journée

Effectuez une recherche sur Excel Downloads...

Macro masquant automatiquement lignes f(x) des cellules nulles (0) d'1 autre colonne

rendan2

XLDnaute Nouveau

Pièces jointes

Efgé

XLDnaute Barbatruc

rendan2

XLDnaute Nouveau

Jacou

XLDnaute Impliqué

Pièces jointes

rendan2

XLDnaute Nouveau

Efgé

XLDnaute Barbatruc

rendan2

XLDnaute Nouveau

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums