Création d’une Plage Dynamique avec VBA – Explication Détaillée, Excel VBA

Création d’une Plage Dynamique avec VBA – Explication Détaillée, Excel VBA 

Concept : Plage Dynamique en VBA 

Une plage dynamique est une plage de cellules dont les dimensions s’ajustent automatiquement en fonction des données présentes dans une feuille Excel. Cela est particulièrement utile lorsque le nombre de lignes ou de colonnes varie fréquemment. 

Objectif 

Nous allons créer un script VBA permettant de : 

• Détecter automatiquement la plage de données en identifiant la dernière ligne et la dernière colonne utilisées. 
• Créer une plage dynamique nommée afin de l’utiliser dans des formules, des graphiques ou des tableaux croisés dynamiques. 
• Appliquer un format visuel pour confirmer la sélection de la plage. 

Code VBA – Création d’une Plage Dynamique 

Ce script VBA : 

1. Identifie la dernière ligne et la dernière colonne utilisées. 
2. Crée une plage dynamique nommée appelée « DynamicData ». 
3. Applique un format visuel à la plage. 
4. Affiche un message de confirmation. 

Sub CreateDynamicRange() 
    Dim ws As Worksheet 
    Dim lastRow As Long, lastCol As Long 
    Dim dynamicRange As Range 
    Dim rangeName As String 
    ' Définition de la feuille de travail (modifier si nécessaire) 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ' Trouver la dernière ligne utilisée dans la colonne A (suppose que les données commencent en A1) 
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 
    ' Trouver la dernière colonne utilisée dans la ligne 1 
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique 
    Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) 
    ' Nommer la plage dynamique 
    rangeName = "DynamicData" 
    ' Supprimer l'ancien nom s'il existe déjà 
    On Error Resume Next 
    ws.Names(rangeName).Delete 
    On Error GoTo 0 
    ' Créer la nouvelle plage dynamique nommée 
    ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange 
    ' Appliquer une couleur à la plage pour la visualiser 
    dynamicRange.Interior.Color = RGB(220, 230, 241) ' Couleur bleu clair 
    ' Message de confirmation 
    MsgBox "La plage dynamique '" & rangeName & "' a été créée avec succès !", vbInformation, "Succès" 
End Sub

Explication du Code 

1️⃣ Détection de la dernière ligne et colonne 

• lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 
  → Recherche la dernière cellule remplie dans la colonne A. 
• lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
  → Recherche la dernière cellule remplie dans la ligne 1. 

2️⃣ Création d’une Plage Dynamique 

• Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) 
  → Définit une plage qui commence en A1 et s’étend jusqu’à la dernière cellule contenant des données. 

3️⃣ Attribution d’un Nom à la Plage 

• Suppression du nom existant : ws.Names(rangeName).Delete 
  → Évite les doublons si le nom « DynamicData » existait déjà. 
• Création d’un nouveau nom : ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange 
  → Permet d’utiliser « DynamicData » directement dans Excel. 

4️⃣ Formatage et Confirmation 

• Mise en forme : dynamicRange.Interior.Color = RGB(220, 230, 241) 
  → Coloration en bleu clair pour repérer la plage dynamique. 
• Affichage d’un message : 
  → Informe l’utilisateur que la plage dynamique a bien été créée. 

Utilisation de la Plage Dynamique 

➡ Dans une formule Excel 

Vous pouvez utiliser « DynamicData » directement dans une cellule : =SUM(DynamicData) 

Cela permettra de s’adapter automatiquement à l’évolution des données. 

➡ Dans un Tableau Croisé Dynamique 

1. Ouvrez Insertion > Tableau Croisé Dynamique. 
2. Sélectionnez « DynamicData » comme source de données. 

➡ Dans un Graphique Dynamique 

1. Insérez un graphique. 
2. Dans la source des données, entrez :  =DynamicData