Choisir les colonnes à importer en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Choisir les colonnes à importer

Sur la base du développement précédent ayant permis d'extraire les données situées aux intersections, nous allons ici apprendre à importer automatiquement les colonnes d'un tableau, choisies manuellement par l'utilisateur.



Sur l'exemple illustré par la capture, nous travaillons à partir d'un tableau alternant des colonnes renseignées et des colonnes vides. Sur la droite, l'utilisateur saisit la succession des numéros de colonnes (1 3 6) qu'il souhaite extraire. A validation, ce sont toutes les données qui sont importées dans un autre tableau sur la droite, sans colonne vierge de séparation. Il s'agit donc d'un procédé intéressant pour réunir des colonnes.

Classeur Excel à télécharger
Pour mener à bien ce nouveau développement, nous suggérons d'appuyer l'étude sur un classeur Excel offrant cette configuration quelque peu particulière.

• Télécharger le classeur effacer-colonnes-vierges.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons bien les deux tableaux avec la configuration de la présentation. Celui de droite est vide à ce stade. Il attend les colonnes à réunir en fonction des positions mentionnées par l'utilisateur en cellule J3. Le tableau des colonnes à regrouper est reconnu sous l'intitulé tab. Vous pouvez facilement le vérifier en déployant la zone Nom en haut à gauche de la feuille Excel.

Code VBA au changement
Pour réunir les rangées de n'importe quel tableau, nous pourrions choisir de développer une nouvelle fonction VBA à ensuite appliquer dans les cellules d'extraction. Mais ici, nous optons pour une solution plus mécanique. Ce regroupement doit s'enclencher automatiquement, dès lors que l'utilisateur modifie les indices de colonnes en cellule J3.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1(Intersections),
• Au-dessus de la feuille de code, déployer la liste déroulante de gauche,
• Puis, choisir l'élément Worksheet,
• Déployer alors la liste de droite,
• Choisir l'événement associé : Change,

C'est ainsi que nous créons la procédure (Worksheet_Change) qui va déclencher son code VBA à la moindre modification de valeur dans l'une des cellules de la feuille active.



Mais naturellement, nous limiterons son champ d'action à la cellule J3. La procédure qui a été automatiquement créée en amont (Worksheet_SelectionChange) n'est plus utile. Elle peut être supprimée.

Les variables
Nous avons maintenant besoin d'un certain nombre de variables, notamment pour recomposer la matrice horizontale des numéros de colonnes à extraire. En effet, nous allons engager un raisonnement matriciel, comme lors du volet précédent, mais en plus simple.

• Dans les bornes de la procédure, ajouter les déclarations de variables suivantes :

...
Dim numCol As String: Dim formule As String
Dim nbCol As Byte: Dim nbLignes As Byte
Dim tbl
...

Nous déclarons les deux premières variables comme des textes (As String). Dans la variable numCol, nous allons construire la matrice des numéros de colonnes fournis par l'utilisateur en cellule J3. Pour extraire toutes les données ensemble, nous exploiterons cette matrice dans l'argument de ligne de la fonction Index. Cette syntaxe, nous l'écrirons dans la variable formule.

Nous déclarons les deux variables suivantes comme des entiers courts (Byte). Leur rôle est de compter le nombre de lignes et de colonnes du tableau résultant pour prévoir la plage d'extraction en conséquence.

Enfin, nous déclarons la variable tbl sans type. Elle doit représenter le tableau nommé tab.

Cibler la cellule modifiée
Ensuite et nous l'avions annoncé, nous souhaitons limiter le champ d'action de cette procédure, afin d'économiser les ressources. Elle doit déclencher son traitement si et seulement si une modification est intervenue en cellule J3. Pour cela, nous allons exploiter l'objet Target passé en paramètre de la procédure. Il représente la cellule modifiée au moment où l'événement VBA se déclenche.

• Après les variables, créer l'instruction conditionnelle suivante :

...
If Target.Address = "$J$3" Then
tbl = Range("tab")
Range("I5:M100").Value = ""
End If
...

C'est la propriété Address de l'objet Target qui renseigne sur les coordonnées de la cellule modifiée. S'il s'agit bien de la cellule J3, Alors (Then) nous poursuivons le traitement. Nous commençons par prélever le tableau dans la variable tbl. Puis, nous réinitialisons les cellules de la plage d'extraction, en prévoyant large.

La matrice des colonnes
Désormais, nous pouvons ambitionner de construire la matrice des numéros de colonnes en fonction des chiffres fournis par l'utilisateur en cellule J3. Comme vous le savez, dans une matrice horizontale avec Excel, chaque élément doit être séparé d'un autre par un point. Et à ce titre, nous avions avancé une succulente astuce dans le volet précédent, en fonction des paramètres régionaux en vigueur qui peuvent varier d'une machine à une autre.

• A la suite du code, dans l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
numCol = "{" & Replace([J3].Value, " ", ",") & "}"
nbCol = Len(Replace([J3].Value, " ", ""))
nbLignes = UBound(tbl, 1)
...

Nous encadrons la suite des numéros récupérés ([J3].Value) par des accolades, pour la construction en bonne et due forme de la matrice. Mais surtout, nous exploitons la fonction Replace pour remplacer tous les espaces par des virgules. En effet, la virgule est le délimiteur par défaut dans le système régional français tandis qu'il s'agit du point pour les Américains. Comme le VBA est un langage américain, chaque virgule sera automatiquement remplacée par un point pour former correctement cette matrice horizontale de numéros. Ensuite et grâce à la fonction Len, nous comptons le nombre de caractères dans cette cellule J3, sans les espaces. C'est ainsi que nous en déduisons le nombre de colonnes à restituer. Puis, grâce à la fonction UBound, nous calculons la hauteur de la première dimension (1) du tableau (tbl). Nous en déduisons donc le nombre de lignes à restituer pour l'extraction.

La formule d'extraction
Il est temps de faire appel à la fonction Index pour bâtir la formule d'extraction sur le tableau tab, à l'aide de la matrice horizontale des colonnes à fournir en troisième argument.

• Toujours dans l'instruction conditionnelle et à la suite, créer le bloc With suivant :

...
formule = "=INDEX(tab, Row(INDIRECT(""1:"" & Rows(tab)))," & numCol & ")"

With Range(Cells(5, 9), Cells(5 + nbLignes - 1, 9 + nbCol - 1))
.FormulaArray = formule
.Value = .Value
End With
...

Tout d'abord, nous construisons la formule d'extraction sur le tableau. En deuxième argument, nous lui passons une matrice verticale virtuelle constituée d'autant de lignes qu'il y a d'extractions à fournir sur la hauteur. Pour interpréter cette matrice, nous engageons la fonction Indirect, comme nous l'avions appris à l'occasion des formations sur les calculs matriciels. En troisième argument, nous lui passons la matrice horizontale des numéros de colonnes à extraire.

Ensuite, nous agissons sur l'intégralité de la plage d'extraction pour répandre les résultats. Nous partons bien de la cellule I5 : Cells(5, 9) pour rejoindre la dernière ligne (5 + nbLignes - 1) de la dernière colonne (9 + nbCol - 1). Les retranchements d'une unité (-1) s'expliquent par le fait que la cellule de départ pour la plage est déjà considérée dans le décompte. Grâce à la propriété FormulaArray, nous y appliquons la formule matricielle. Puis, nous écrasons ces résultats (.Value = .Value) pour ne garder que les valeurs sans les formules.

Pour finir, nous devons tester cette solution VBA de regroupement des informations.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• En cellule J3, changer les numéros de colonnes comme suit : 3 6, avec un espace,
• Puis, valider par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, ce sont les deux colonnes mentionnées qui sont rapatriées et réunies à partir du tableau d'origine.