Créer une plage dynamique, Excel VBA

Créer une plage dynamique, Excel VBA

Voici une explication détaillée sur la manière de créer une plage dynamique avec VBA dans Excel. Une plage dynamique permet d’adapter automatiquement la plage de données en fonction des ajouts ou suppressions de lignes et de colonnes, ce qui est particulièrement utile pour travailler avec des ensembles de données changeants.
Objectif :
Créer une plage dynamique en utilisant VBA qui s’ajuste automatiquement lorsque des lignes ou des colonnes sont ajoutées ou supprimées dans une feuille de calcul.
Explication étape par étape :
1. Identifier les points de départ et de fin : Pour créer une plage dynamique, vous devez d’abord identifier les points de départ et de fin de la plage de données. Par exemple, le point de départ pourrait être la première cellule de données, et le point de fin serait la dernière cellule remplie dans le jeu de données. Nous utiliserons la propriété End de l’objet Range pour trouver ces points de manière dynamique.
2. Définir la plage de manière dynamique : Nous allons utiliser la fonction Range combinée avec Cells et End pour trouver la dernière ligne et la dernière colonne de données. Les propriétés End(xlDown) et End(xlToRight) sont utilisées pour naviguer à travers les données et trouver la dernière ligne et colonne utilisées.
3. Utiliser une plage nommée (optionnel) : Vous pouvez également affecter la plage dynamique à une plage nommée afin de pouvoir y faire référence facilement dans le classeur.
Exemple de code VBA :

Sub CreerPlageDynamique()
    ' Déclaration des variables
    Dim ws As Worksheet
    Dim celluleDeDepart As Range
    Dim derniereLigne As Long
    Dim derniereColonne As Long
    Dim plageDynamique As Range
    ' Définir la feuille de travail
    Set ws = ThisWorkbook.Sheets("Feuil1") ' Modifier le nom de la feuille si nécessaire
    ' Définir la cellule de départ (coin supérieur gauche des données)
    Set celluleDeDepart = ws.Range("A1") ' Supposons que les données commencent à la cellule A1
    ' Trouver la dernière ligne avec des données dans la feuille
    derniereLigne = ws.Cells(ws.Rows.Count, celluleDeDepart.Column).End(xlUp).Row
    ' Trouver la dernière colonne avec des données dans la feuille
    derniereColonne = ws.Cells(celluleDeDepart.Row, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set plageDynamique = ws.Range(celluleDeDepart, ws.Cells(derniereLigne, derniereColonne))
    ' Optionnel : Affecter la plage dynamique à une plage nommée
    ThisWorkbook.Names.Add Name:="PlageDynamique", RefersTo:=plageDynamique
    ' Exemple : Afficher la plage dynamique dans une boîte de message
    MsgBox "La plage dynamique de " & plageDynamique.Address & " a été créée !", vbInformation
End Sub

Explication détaillée du code :
1. Définir la feuille de travail :
Set ws = ThisWorkbook.Sheets("Feuil1")
Cette ligne définit la feuille de travail ws comme étant la feuille « Feuil1 ». Vous devez remplacer « Feuil1 » par le nom réel de votre feuille.
2. Définir la cellule de départ :
Set celluleDeDepart = ws.Range("A1")
Ici, nous définissons le point de départ de la plage de données comme étant la cellule A1. Vous pouvez ajuster cela à la première cellule de vos données réelles.
3. Trouver la dernière ligne et la dernière colonne :

• • La derniereLigne est déterminée par cette ligne de code :

derniereLigne = ws.Cells(ws.Rows.Count, celluleDeDepart.Column).End(xlUp).Row

Cette ligne trouve la dernière ligne remplie dans la colonne donnée en remontant depuis la toute dernière ligne de la feuille de calcul (le nombre de lignes dans Excel est renvoyé par ws.Rows.Count).

• • La derniereColonne est déterminée par :

derniereColonne = ws.Cells(celluleDeDepart.Row, ws.Columns.Count).End(xlToLeft).Column

Cette ligne trouve la dernière colonne remplie dans la première ligne de données, en se déplaçant vers la gauche depuis la toute dernière colonne.
4. Définir la plage dynamique :
Set plageDynamique = ws.Range(celluleDeDepart, ws.Cells(derniereLigne, derniereColonne))
La plage dynamique est ensuite définie à l’aide de la fonction Range, où celluleDeDepart représente le coin supérieur gauche de la plage, et le coin inférieur droit est déterminé par les variables derniereLigne et derniereColonne.
5. Plage nommée (optionnel) :
ThisWorkbook.Names.Add Name:="PlageDynamique", RefersTo:=plageDynamique
Cette ligne optionnelle permet de nommer la plage dynamique, ce qui vous permet de la référencer facilement dans d’autres formules ou codes VBA.
6. Afficher l’adresse de la plage dans une boîte de message :
MsgBox "La plage dynamique de " & plageDynamique.Address & " a été créée !", vbInformation
Enfin, une boîte de message s’affiche pour informer l’utilisateur que la plage dynamique a été créée et afficher son adresse.
Avantages de cette approche :

• Ajustement automatique : La plage sera automatiquement mise à jour lorsque des lignes ou des colonnes seront ajoutées ou supprimées.
• Réutilisabilité : Vous pouvez utiliser le nom de la plage (par exemple, « PlageDynamique ») dans d’autres formules ou procédures VBA, ce qui simplifie la manipulation des données.

Cas d’utilisation :
Cette méthode est utile dans des scénarios tels que :

• Des tableaux de données où des lignes et des colonnes sont fréquemment ajoutées ou supprimées.
• La création de graphiques dynamiques qui doivent ajuster leurs plages de données.
• L’utilisation de plages dynamiques dans des formules complexes ou des tableaux croisés dynamiques.