Actions sur plages / cellules non contigues définies à partir d'une ligne active

**Barbenault** · 03/10/2014, 13h41

Bonjour et merci d'avance pour le soutien que vous m'apporterez.

Je dispose d'un fichier dont voici le code complet (à la fin de ce message), une partie de ce code me pose problème car ne répond pas à mes attentes. L'objectif de cet macro est d'insérer une ligne dans un tableau (je veux dupliquer une ligne, cette ligne s'insére en dessous de celle selectionnée). Jusque là tout va bien.

J'aimerais donc pour améliorer ce code, ajouter un élément, il faudrait que la ligne soit dupliquée, mais que les valeurs de certaines cellules soient supprimées.
Ces cellules sont situées sur des colonnes fixes, non contigues et sur la ligne insérée. Pouvons nous définir cette plage par une variable?

Dans mon exemple, prenons les colonnes G,H,J,K,M,N et la ligne en dessous de la ligne active. Si je parviens à ce résultat, j'imagine que ce sera plus simple d'effacer le contenu de cette plage, non? Mais je ne sais pas définir cette plage.

Autre option : ces cellules possède une caractéristique commune qui ne renvoie pas à la référence de la colonne, mais au fait quelles sont non protégées. Même question que précédemment, pouvons nous définir une variable qui sélectionne sur une ligne active (ou celle en-dessous plutôt), toutes les cellules non protégées... j'ai essayé le code ci-dessous mais cela ne fonctionne pas puisque'il prend toute la ligne et non pas cellule par cellule.

Est-ce que l'une de ces 2 options vous paraît pertinente et réalisable.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
If Not ActiveCell(2).EntireRow.Locked = False Then
ActiveCell(2).EntireRow.ClearContents

Le code complet...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
Sub forecast_insert()
 
'Cette macro permet d'insérer une ligne dans le document
 
' Ci après, nous définissons 3 variables :
 
    ' Soit la dernière ligne du tableau ("DernièreLigne")
    ' Soit "x", la ligne de la cellule active
    ' Soit "y", qui renvoie à la cellule située dans la première colonne de la ligne sélectionnée par l'utilisateur
 
Dim DernièreLigne As Long
Dim x As Long
Dim y As String
 
' Définition de la zone cible, càd, la plage dans laquelle peut être inserée une ligne
' Note : la dernière ligne est définie comme étant la dernière ligne non-vide, pour obtenir une ligne non vide, j'ai saisi "MSF" dans la cellule choisie.
 
    x = ActiveCell.Row
    y = Cells(x, 1)
    DernièreLigne = ActiveSheet.Range("AQ9").End(xlDown).Row
 
' Si l'utilisateur lance la commande en dehors de cette plage (ligne 9 à "dernière ligne"), la commande s'arrête
' Note : "MSF" est saisi dans la colonne "AQ", la formule est donc appliquée sur cette colonne
 
If Not ((x > 9) And (x < DernièreLigne)) Then
Exit Sub
End If
 
' Si l'utilisateur lance la commande dans une ligne dont la première colonne n'e contient pas "***", la commande s'arrête
 
If Not (y = "") Then
Exit Sub
End If
 
'Une fois ces deux conditions validées, la commande est lancée
 
'Déprotection de la feuille dont le mot de passe est "MSF"
 
    ActiveSheet.Unprotect Password:="MSF"
 
' Insère une nouvelle ligne en-dessous de celle qui est sélectionnée (format numérique, cellule dévérouillée)
 
    ActiveCell(2).EntireRow.Insert
    ActiveCell(1).EntireRow.Copy ActiveCell(2).EntireRow
    If Not ActiveCell(2).EntireRow.Locked = False Then
    ActiveCell(2).EntireRow.ClearContents
    End If
 
    'ActiveCell(2).EntireRow.NumberFormat = "#,##0.00"
    'ActiveCell(2).EntireRow.Locked = False
 
'Protection de la feuille dont le mot de passe est "MSF"
 
    ActiveSheet.Protect UserInterfaceOnly:=True, Password:="MSF", DrawingObjects:=True, Contents:=True, Scenarios:=False _
, AllowSorting:=True, AllowFiltering:=True, AllowFormattingCells:=True, AllowFormattingColumns:=False, _
AllowFormattingRows:=False
 
 
 End Sub

Merci d'avance pour votre aide

**Menhir** · 03/10/2014, 14h19

Envoyé par Barbenault

il faudrait que la ligne soit dupliquée, mais que les valeurs de certaines cellules soient supprimées.

Il suffit d'appliquer un ClearContents à des Range (ou cells) correspondant aux cellules désirées.
A la limite, tu l'écris une fois et tu la recopies 5 fois en changeant la désignation de colonne.

**Barbenault** · 03/10/2014, 15h17

Merci Menhir, évidemment c'est une solution, mais j'espère trouver quelque chose de plus abouti. Que penses-tu de la piste "cellules non vérouillées?
En fait, la plage que je dois définir comporte pas moins de 12 plages non contigues (composée chacune de 2 cellules contigues). C'est plage seront toujours sur les mêmes colones mais sur une ligne variable.

D'après ton conseil, je devrais définir 12 plages et leur appliquer un ClearContents, moi je souhaiterais plutôt définir un Range qui regroupe ces douzes plages et c'est là que je bloque.

**keygen08** · 03/10/2014, 15h23

bonjour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("S2,S5,S10,S15").ClearContents

Je l'ai fait en vertical mais fonctionne également en horizontal

**Barbenault** · 03/10/2014, 15h38

Donc, si je considère ton exemple, je l'adapte à mon fichier (je reste en vertical pour reformuler selon ton exemple), cela donnerait quelquechose comme cela

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("S2:S3,S5:S6,S8:S9,...,").ClearContents

Maintenant, je ne comprends pas comment remplacer le "S" par une variable car selon la sélection de l'utilisateur je pourrais avoir besoin de la formule suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("D2:D3,D5:D6,SD:D9,...,").ClearContents

En somme, l'une des deux coordonnées change, pas l'autre, comment dans cette exemple associer la colonne active (ou ligne active pour mon cas) avec l'autre coordonnée qui est fixe?

Je sais, je ne suis pas doué, c'est difficile de s'exprimer dans une langue que l'on a jamais apprise.

**Menhir** · 03/10/2014, 15h45

Envoyé par Barbenault

D'après ton conseil, je devrais définir 12 plages et leur appliquer un ClearContents, moi je souhaiterais plutôt définir un Range qui regroupe ces douzes plages et c'est là que je bloque.

Je suis pragmatique : recopier 11 fois et changer les désignations, ça prend une vingtaine de secondes sans risque.
Essayer de faire un truc plus compliqué, ça va te prendre des heures de réflexion et d'ajustement.
Entre les deux, je n'hésite pas.

La solution de keygen te permettra de mettre tout ça sur une seule ligne.

Envoyé par Barbenault

cela donnerait quelquechose comme cela

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("S2:S3,S5:S6,S8:S9,...,").ClearContents

Il serait plus simple de ne mettre que des ',' plutôt que d'alterner "," et ":" et ça reviendrait au même (dans ton cas).

Maintenant, je ne comprends pas comment remplacer le "S" par une variable

C'est tout simple.
Ce qui est dans le Range est une simple chaine de caractère. Donc, ce qui est constant est entre guillemets relié au variables par des & (ou des +).

De la même façon qu'on peut écrire Range("A" & MaVariable) ou Range(MaVariable & "1")

**Barbenault** · 03/10/2014, 15h52

Bonjour keygen08, je n'avais pas fait attention que cette proposition venait de toi.
D'accord pour être pragmatique, mais comment puis-je écrire correctement associer la coordonnée fixe et la variable... est-ce que ce qui suit est correct?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("G" & ActiveCell.Row & ":H" & ActiveCell.Row).ClearContents

et donc je répère autant de fois que nécessaire...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("J" & ActiveCell.Row & ":K" & ActiveCell.Row).ClearContents

**Menhir** · 03/10/2014, 15h55

Envoyé par Barbenault

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("J" & ActiveCell.Row & ":K" & ActiveCell.Row).ClearContents

Ou bien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
X = ActiveCell.Row
Range("G" & x & ",H" & x & ",J" & x & ",K" & x).ClearContents

**Barbenault** · 03/10/2014, 16h16

Merci Menhir pour ta patience, je vais tester tout cela et te fais un retour.

Edit : oui c'est parfait, j'ai juste une petite adapatation à faire. Je te remercie encore pour tes conseils.

Edit 2 : Voilà le code avec une simple adaptation qui permet d'appliquer le ClearContents sur la ligne insérée et non pas sur la ligne au dessus

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
X = ActiveCell.Row
Range("G" & x+1 & ",H" & x+1 & ",J" & x & ",K" & x+1).ClearContents

**eriiic** · 03/10/2014, 20h01

Bonjour,

je reviens à une partie de la question : définir une plage
Tu pourrais faire ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Dim plage As Range
Set plage = Range("G1:H1,J1:K1")
'....
plage.Offset(ta_ligne - 1).ClearContents

eric

**Barbenault** · 06/10/2014, 11h02

Bonjour Eric et merci pour ta réponse,

En fait j'avais suivi les conseils donnés précédemment ce qui donnait quelque chose de pas très esthétique...

Je viens de tester ta propositon et je trouve cela beaucoup mieux, je t'en remercie, tes précieux conseils m'aident à progresser.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Dim Plage As Range
Dim x As Long
 ...
 Set Plage = Range("G1:H1,J1:K1,M1:N1,P1:Q1,S1:T1,V1:W1,Y1:Z1,AB1:AC1,AE1:AF1,AH1:AI1,AK1:AL1,AN1:AO1")
 x = ActiveCell.Row
 ...
 Plage.Offset(x).ClearContents

**Menhir** · 06/10/2014, 11h15

Plus simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
x=x+1
For i=7 to 40 step 3
    Cells(x,i).ClearContents
    Cells(x,i+1).ClearContents
Next i

Ou bien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
x=x+1
For i=7 to 41
    If i Mod 3 <> 0 ThenCells(x,i).ClearContents
Next i

**keygen08** · 06/10/2014, 11h18

Bonjour
Sinon il y a ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 Sub efface()
 Dim ArrWd
 dim c as byte
 ArrWd = Split("g;h;j;k;m;n;p;q;s;t;v;w;y;z;ab;ac;ae;af;ah;ai;ak;al;ab;ao", ";")
    For c = 0 To UBound(ArrWd)
    Range(ArrWd(c) & x + 1).ClearContents
    Next c
 End Sub

**Barbenault** · 06/10/2014, 11h33

Merci à tous pour vos contributions et le temps que vous consacrez à des amateurs dans mon genre.
Pour ceux qui liront cette discussion, ces différentes propositions seront certainement très utiles.
Excellente journée à vous tous.

Actions sur plages / cellules non contigues définies à partir d'une ligne active [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager