Créer une autonomisation de la plage dynamique, Excel VBA
Voici une explication détaillée ainsi qu’un code VBA pour créer une plage dynamique (« dynamic range ») dans Excel. Cette fonctionnalité vous permet de créer des plages qui s’ajustent automatiquement en fonction des données présentes dans la feuille de calcul.
Objectif :
Nous voulons créer une plage dynamique dans Excel à l’aide de VBA, qui s’ajuste automatiquement à mesure que des données sont ajoutées ou supprimées.
Explication détaillée :
1. Comprendre les Plages Dynamiques : Une plage dynamique dans Excel fait référence à une plage dont la taille s’ajuste automatiquement en fonction des données présentes. Cela est utile lorsque vous travaillez avec des ensembles de données qui peuvent augmenter ou diminuer au fil du temps.
2. VBA pour créer une Plage Dynamique : Nous allons écrire une fonction VBA qui définira une plage dynamique en recherchant la dernière ligne et la dernière colonne avec des données, et ensuite ajustera la plage en conséquence.
3. Utilisation des Noms de Plage : Un « Nom de Plage » dans Excel permet de référencer facilement une plage de cellules. Nous utiliserons VBA pour définir un nom de plage dynamique qui sera mis à jour automatiquement.
Code VBA pour créer une plage dynamique :
Sub CreerPlageDynamique()
Dim ws As Worksheet
Dim DerniereLigne As Long
Dim DerniereColonne As Long
Dim PlageDynamique As Range
' Définir la feuille de travail à utiliser (Changer "Feuil1" par le nom de la feuille souhaitée)
Set ws = ThisWorkbook.Sheets("Feuil1")
' Trouver la dernière ligne contenant des données dans la colonne A (vous pouvez changer la colonne si nécessaire)
DerniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Trouver la dernière colonne contenant des données dans la ligne 1 (vous pouvez changer la ligne selon les données)
DerniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Définir la plage dynamique
Set PlageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(DerniereLigne, DerniereColonne))
' Optionnel : Donner un nom à cette plage (pour y faire référence facilement dans des formules)
ws.Names.Add Name:="PlageDynamique", RefersTo:=PlageDynamique
' Afficher un message avec l'adresse de la plage dynamique
MsgBox "La plage dynamique est : " & PlageDynamique.Address
End Sub
Explication du code :
1. Définir la feuille de travail :
- La variable ws représente la feuille de calcul sur laquelle la plage sera définie. Dans cet exemple, elle est définie comme « Feuil1 », mais vous pouvez la changer selon votre feuille de calcul.
2. Trouver la dernière ligne :
- La variable DerniereLigne est calculée en utilisant la méthode .End(xlUp) sur la colonne A. Cela permet de trouver la dernière cellule non vide de la colonne A en partant du bas de la feuille et en remontant.
3. Trouver la dernière colonne :
- De manière similaire, DerniereColonne est calculée en utilisant la méthode .End(xlToLeft) sur la ligne 1. Cela permet de trouver la dernière cellule non vide de la ligne 1, ce qui garantit que la plage s’adapte à la largeur de vos données.
4. Définir la plage dynamique :
- La variable PlageDynamique est définie en prenant les cellules allant de (1,1) (en haut à gauche) à la cellule correspondant à la dernière ligne et colonne avec des données. Cela permet de créer une plage flexible qui s’ajuste automatiquement en fonction des données présentes.
5. Nommer la plage :
- En utilisant la méthode ws.Names.Add, nous attribuons le nom « PlageDynamique » à la plage nouvellement définie. Vous pouvez ensuite utiliser ce nom dans vos formules ou dans d’autres parties du code VBA pour faire référence à cette plage dynamique.
6. Affichage d’un message :
- Un message s’affiche avec l’adresse de la plage dynamique, ce qui vous permet de vérifier que la plage a bien été définie.
Utilisation de la Plage Dynamique :
Une fois que la plage dynamique est créée, vous pouvez l’utiliser dans vos formules ou dans d’autres parties de votre feuille de calcul. Par exemple :
- Dans une formule :
=SOMME(PlageDynamique) - Dans VBA :
Range("PlageDynamique").Select
Avantages des Plages Dynamiques :
- Ajustement automatique : La plage s’ajuste automatiquement lorsque des données sont ajoutées ou supprimées.
- Référencement facile : Les plages nommées rendent les références de plages complexes plus simples dans les formules.
- Intégrité des données : Cela évite de référencer des cellules vides ou non pertinentes dans vos plages de données.
Remarques :
- Vous pouvez ajuster les colonnes et lignes utilisées pour trouver la dernière cellule de données en fonction de la structure de votre tableau. Par exemple, si vos données commencent dans la colonne B, vous devez remplacer « A » par « B » pour le calcul de DerniereLigne.
- Si vous avez des conditions spécifiques pour la création de la plage (par exemple, ignorer certaines lignes vides), vous pouvez ajouter des conditions supplémentaires dans le code.