Créer et implémenter une plage dynamique, Excel VBA

Créer et implémenter une plage dynamique, Excel VBA

Voici un code VBA détaillé pour créer et implémenter une plage dynamique dans Excel, accompagné d’une explication complète en français. 

Implémentation : Créer une Plage Dynamique avec VBA 

Une plage dynamique dans Excel est une plage nommée qui s’étend ou se contracte automatiquement en fonction des données présentes. Cela est particulièrement utile lorsqu’un jeu de données évolue au fil du temps et que vous souhaitez que les formules, graphiques ou tableaux croisés dynamiques fassent toujours référence aux dernières données. 

Dans VBA, vous pouvez implémenter des plages dynamiques en utilisant : 

1. Les plages nommées avec VBA 
2. La détection de la dernière ligne et colonne utilisées 
3. Redimensionner une plage nommée de manière dynamique 

Code VBA : Créer une Plage Dynamique 

Voici un script VBA complet qui : 

• Trouve la dernière ligne et la dernière colonne utilisées dans une feuille de calcul donnée. 
• Crée une plage nommée dynamique en fonction des données détectées. 
• Associe cette plage nommée à une variable pour une utilisation ultérieure. 

Sub CreateDynamicRange() 
    Dim ws As Worksheet 
    Dim lastRow As Long, lastCol As Long 
    Dim dynamicRange As Range 
    Dim rangeName As String 
    ' Définir la feuille de calcul 
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modifier le nom de la feuille si nécessaire 
    ' Trouver la dernière ligne utilisée dans la colonne A (modifier si nécessaire) 
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 
    ' Trouver la dernière colonne utilisée dans la ligne 1 (modifier si nécessaire) 
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique 
    Set dynamicRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol)) ' Exclut les en-têtes 
    ' Définir un nom pour la plage dynamique 
    rangeName = "PlageDynamique" 
    ' Supprimer l'éventuelle plage nommée existante 
    On Error Resume Next 
    ws.Names(rangeName).Delete 
    On Error GoTo 0 
    ' Créer une nouvelle plage nommée 
    ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange 
    ' Confirmation de la création 
    MsgBox "La plage dynamique '" & rangeName & "' a été créée depuis " & _ 
           dynamicRange.Address, vbInformation, "Plage Dynamique Créée" 
    ' Nettoyage 
    Set dynamicRange = Nothing 
    Set ws = Nothing 
End Sub

Explication du Code : 

Identifier la Dernière Ligne et Colonne Utilisées 

• La fonction Cells(Rows.Count, 1).End(xlUp).Row permet de trouver la dernière ligne utilisée dans la colonne A. 
• La fonction Cells(1, Columns.Count).End(xlToLeft).Column permet de trouver la dernière colonne utilisée dans la ligne 1. 

Définir la Plage Dynamique 

• La plage commence à partir de la cellule A2 (en supposant que les en-têtes se trouvent en ligne 1) jusqu’à la dernière ligne et colonne détectées. 

Créer une Plage Nomée 

• Le code commence par supprimer toute plage nommée existante appelée « PlageDynamique » afin d’éviter les conflits. 
• Ensuite, une nouvelle plage nommée « PlageDynamique » est créée et pointe vers la plage mise à jour. 

Confirmation à l’Utilisateur 

• Une boîte de message apparaît pour afficher l’adresse de la plage dynamique créée. 

Exemple de Résultat 

Scénario : 

Supposons que Sheet1 ait les données suivantes : 

Lorsque vous exécutez la macro, elle détecte dynamiquement que la dernière ligne est 4 et la dernière colonne est 3, et crée une plage nommée de A2:C4. 

Le message affiché sera : 

La plage dynamique 'PlageDynamique' a été créée depuis $A$2:$C$4 

Avantages de cette Méthode 

✔ Mises à jour automatiques – La plage s’actualise automatiquement lorsque de nouvelles données sont ajoutées. 
✔ Utilisation dans des formules et des tableaux croisés dynamiques – La plage nommée « PlageDynamique » peut être utilisée dans des formules comme SOMME, NB.SI, ou dans un tableau croisé dynamique. 
✔ Pas besoin de réajustement manuel – Vous n’avez pas à redéfinir manuellement la plage lorsque les données changent.