Création d’une plage dynamique, Excel VBA

Création d’une plage dynamique, Excel VBA

Voici une explication détaillée sur la création d’une plage dynamique avec VBA dans Excel. 

Objectif : 

Nous voulons créer une plage dynamique avec VBA qui s’ajuste automatiquement lorsque de nouvelles données sont ajoutées ou supprimées. Cela permet de maintenir la plage de données à jour, et toutes les formules ou graphiques associés à cette plage seront également mis à jour automatiquement. 

Approche : 

1. Utilisation de la méthode Names.Add pour créer une plage dynamique. 
2. Utilisation des méthodes Offset et CountA pour calculer la taille de la plage dynamiquement. 
3. Utilisation des objets Workbook ou Worksheet pour délimiter la portée de la plage. 

Code VBA détaillé : 

Sub CreerPlageDynamique() 
    Dim ws As Worksheet 
    Dim DerniereLigne As Long 
    Dim DerniereColonne As Long 
    Dim PlageDynamique As String 
    ' Définir la feuille de calcul où vous voulez créer la plage dynamique 
    Set ws = ThisWorkbook.Sheets("Feuil1") ' Changez "Feuil1" par le nom de votre feuille 
    ' Trouver la dernière ligne utilisée dans la colonne A (ou la colonne qui contient toujours des données) 
    DerniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    ' Trouver la dernière colonne utilisée dans la ligne 1 (ou la ligne contenant des données) 
    DerniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la formule pour la plage dynamique 
    ' Cela crée une plage de A1 à la dernière cellule utilisée (en fonction de vos données) 
    PlageDynamique = "Feuil1!$A$1:$" & ws.Cells(1, DerniereColonne).Address(False, False) & "$" & DerniereLigne 
    ' Créer la plage dynamique en ajoutant un nom à la plage 
    ' Ici, nous ajoutons la plage dynamique comme plage nommée "PlageDynamique" 
    ThisWorkbook.Names.Add Name:="PlageDynamique", RefersTo:="=" & PlageDynamique 
    ' Optionnel : Confirmer que la plage dynamique a été créée 
    MsgBox "La plage dynamique 'PlageDynamique' a été créée de A1 à " & ws.Cells(DerniereLigne, DerniereColonne).Address  
End Sub

Explication du Code : 

1. Références : 

• La variable ws fait référence à la feuille de calcul où la plage dynamique sera créée. 
• Les variables DerniereLigne et DerniereColonne sont utilisées pour trouver la dernière ligne et la dernière colonne utilisées dans la feuille de calcul. 

2. Trouver la dernière ligne et la dernière colonne : 

• DerniereLigne = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row : Cette ligne trouve la dernière ligne avec des données dans la colonne A. Elle commence en bas de la feuille et se déplace vers le haut jusqu’à ce qu’elle trouve des données. 
• DerniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column : Cette ligne trouve la dernière colonne utilisée dans la ligne 1 en commençant par la colonne la plus à droite et en se déplaçant vers la gauche jusqu’à ce qu’elle trouve des données. 

3. Formule de la plage dynamique : 

• La variable PlageDynamique est utilisée pour construire la référence à la plage dynamique sous la forme Feuil1!$A$1:$[dernière colonne][dernière ligne]. Par exemple, si les données se terminent à la colonne D et à la ligne 20, la plage sera Feuil1!$A$1:$D$20. 

4. Création de la plage nommée : 

• ThisWorkbook.Names.Add Name:= »PlageDynamique », RefersTo:= »= » & PlageDynamique : Cette ligne crée une plage dynamique nommée « PlageDynamique ». L’argument RefersTo définit la plage que le nom fait référence. 

5. Confirmation : 

• La fonction MsgBox est utilisée pour afficher un message confirmant que la plage dynamique a été créée avec succès. 

Comment ça fonctionne : 

• Ajustement dynamique : Lorsque vous ajoutez de nouvelles données à la feuille, la plage nommée « PlageDynamique » s’ajustera automatiquement pour inclure les nouvelles lignes et colonnes en fonction de la taille actuelle des données. 
• Mises à jour en temps réel : Cette plage dynamique est mise à jour chaque fois que le classeur est ouvert ou que de nouvelles données sont ajoutées et que la macro est exécutée. 

Comment l’utiliser : 

1. Ouvrez le classeur où vous souhaitez créer une plage dynamique. 
2. Appuyez sur ALT + F11 pour ouvrir l’éditeur VBA. 
3. Dans l’éditeur VBA, insérez un nouveau module en cliquant sur Insertion > Module. 
4. Collez le code ci-dessus dans le module. 
5. Exécutez la macro CreerPlageDynamique pour créer la plage dynamique. 

Vous pouvez utiliser cette plage nommée dans des formules ou des graphiques, et elle sera automatiquement mise à jour en fonction de la taille de vos données. 

Modifications possibles : 

• Différentes colonnes/lignes : Vous pouvez modifier le code pour cibler différentes colonnes ou lignes. Par exemple, si vos données commencent dans la colonne B au lieu de A, modifiez ws.Cells(ws.Rows.Count, « A ») par ws.Cells(ws.Rows.Count, « B »). 
• Plages multiples : Si vous souhaitez créer plusieurs plages dynamiques, vous pouvez répliquer la logique dans une boucle ou comme des plages nommées séparées, chacune avec ses propres calculs de lignes/colonnes.