Créer des plages dynamiques, Excel VBA

Créer des plages dynamiques, Excel VBA

Créer des plages dynamiques dans Excel en utilisant VBA permet d’avoir plus de flexibilité et d’évolutivité dans vos classeurs. Lorsque vous travaillez avec des plages de données susceptibles de changer en taille (par exemple, lorsque de nouvelles données sont ajoutées ou supprimées), les plages dynamiques garantissent que vos formules et scripts VBA sont toujours précis. Voici une explication détaillée de la façon de créer et de communiquer avec des plages dynamiques en VBA. 

Concept des Plages Dynamiques dans Excel 

Une plage dynamique est une plage qui ajuste automatiquement sa taille pour inclure toutes les données pertinentes. Cela est particulièrement utile lorsque vous avez un jeu de données qui peut croître ou rétrécir au fil du temps. Par exemple, si vous travaillez avec un tableau où de nouvelles lignes ou colonnes sont régulièrement ajoutées, vous souhaitez que la plage dans votre script VBA s’adapte automatiquement sans avoir à la modifier manuellement. 

Comment Créer une Plage Dynamique en VBA 

En VBA, les plages dynamiques peuvent être créées en utilisant différentes méthodes, telles que CurrentRegion, End, ou Resize. Passons en revue ces méthodes avec des exemples. 

1. Utilisation de CurrentRegion

La propriété CurrentRegion est utile lorsque vous avez un bloc de données contigu. Elle ajuste automatiquement la plage pour inclure toutes les données autour de la cellule, jusqu’à une ligne ou une colonne vide. 

Sub CreerPlageDynamique_CurrentRegion() 
    Dim ws As Worksheet 
    Dim plageDynamique As Range 
    Set ws = ThisWorkbook.Sheets("Feuille1") 
    ' Définir la plage à partir de A1 
    Set plageDynamique = ws.Range("A1").CurrentRegion 
    ' Sélectionner la plage dynamique 
    plageDynamique.Select 
End Sub

Explication : 

• Le code définit une feuille de calcul et utilise Range(« A1 »).CurrentRegion pour créer une plage dynamique à partir de la cellule A1. 
• CurrentRegion inclura automatiquement toutes les cellules autour de A1 qui font partie du bloc de données contigu. 
• La plage dynamique est ensuite sélectionnée. 

2. Utilisation de End pour Trouver la Dernière Ligne/Colonne

Une autre méthode pour créer des plages dynamiques consiste à utiliser la propriété End pour trouver la dernière ligne ou colonne contenant des données. Cela est particulièrement utile si vous savez que vos données se trouvent dans une colonne ou une ligne spécifique. 

Sub CreerPlageDynamique_End() 
    Dim ws As Worksheet 
    Dim derniereLigne As Long 
    Dim derniereColonne As Long 
    Dim plageDynamique As Range 
    Set ws = ThisWorkbook.Sheets("Feuille1") 
    ' Trouver la dernière ligne et colonne utilisées dans la feuille 
    derniereLigne = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    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)) 
    ' Sélectionner la plage dynamique 
    plageDynamique.Select 
End Sub

Explication : 

• Ce code trouve la dernière ligne utilisée dans la colonne A et la dernière colonne utilisée dans la ligne 1. 
• Il crée ensuite une plage dynamique allant de la cellule A1 jusqu’à la dernière cellule utilisée dans la dernière ligne et colonne. 
• La plage dynamique est ensuite sélectionnée. 

3. Utilisation de Resize pour des Plages Dynamiques

La méthode Resize vous permet d’agrandir ou de réduire une plage existante en fonction du nombre de lignes ou de colonnes dont vous avez besoin. Cela peut être utile lorsque vous avez une plage spécifique que vous souhaitez étendre ou réduire dynamiquement. 

Sub CreerPlageDynamique_Resize() 
    Dim ws As Worksheet 
    Dim plageDynamique As Range 
    Dim nbLignes As Long 
    Dim nbColonnes As Long 
    Set ws = ThisWorkbook.Sheets("Feuille1") 
    ' Compter le nombre de lignes et de colonnes dans les données 
    nbLignes = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    nbColonnes = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique à partir de A1 
    Set plageDynamique = ws.Range("A1").Resize(nbLignes, nbColonnes) 
    ' Sélectionner la plage dynamique 
    plageDynamique.Select 
End Sub

Explication : 

• Ce script utilise la méthode Resize pour ajuster dynamiquement la taille de la plage à partir de A1. 
• Il calcule le nombre de lignes et de colonnes contenant des données et redimensionne la plage en conséquence. 
• La plage dynamique est ensuite sélectionnée. 

Comment Communiquer avec les Plages Dynamiques en VBA 

Une fois que vous avez créé une plage dynamique, vous pouvez communiquer avec elle de différentes manières, telles que la copie, le formatage ou l’utilisation dans une formule. Voici quelques exemples. 

1. Copier la Plage Dynamique

Vous pouvez copier la plage dynamique vers un autre emplacement. 

Sub CopierPlageDynamique() 
    Dim ws As Worksheet 
    Dim plageDynamique As Range 
    Set ws = ThisWorkbook.Sheets("Feuille1") 
    Set plageDynamique = ws.Range("A1").CurrentRegion 
    ' Copier la plage dynamique 
    plageDynamique.Copy Destination:=ws.Range("D1") 
End Sub

Explication : 

• Ce code copie la plage dynamique définie par CurrentRegion à partir de A1 et la colle dans D1. 

2. Appliquer une Formule à la Plage Dynamique

Vous pouvez appliquer des formules aux plages dynamiques en utilisant VBA. 

Sub AppliquerFormulePlageDynamique() 
    Dim ws As Worksheet 
    Dim plageDynamique As Range 
    Set ws = ThisWorkbook.Sheets("Feuille1") 
    Set plageDynamique = ws.Range("A1").CurrentRegion 
    ' Appliquer une formule à la plage dynamique 
    plageDynamique.Offset(0, 1).Formula = "=SOMME(A2:A" & plageDynamique.Rows.Count & ")" 
End Sub

Explication : 

• Ce script ajoute une formule qui somme les valeurs de la colonne A, en partant de A2 jusqu’à la dernière ligne de la plage dynamique, et place le résultat dans la colonne voisine. 

Conclusion 

Les plages dynamiques en VBA offrent une grande flexibilité, garantissant que vos scripts peuvent gérer des données qui augmentent ou diminuent. Les méthodes décrites — CurrentRegion, End et Resize — permettent une gestion efficace des plages qui s’ajustent aux modifications de la feuille de calcul. Une fois la plage dynamique définie, vous pouvez l’utiliser dans diverses opérations comme la copie, le collage et l’application de formules. 

En utilisant des plages dynamiques, vous rendez votre code VBA plus robuste, lui permettant de s’adapter automatiquement aux données changeantes de votre classeur.