Créez une plage dynamique dans un contexte de leadership, Excel VBA

Créez une plage dynamique dans un contexte de leadership, Excel VBA

Voici un code VBA détaillé pour créer une plage dynamique dans Excel, accompagné d’une explication approfondie. 

Concept : Création d’une plage dynamique avec VBA 

Une plage dynamique s’ajuste automatiquement en fonction du nombre de lignes ou de colonnes contenant des données. Bien qu’Excel propose des plages dynamiques via des formules (OFFSET, INDEX), VBA permet une gestion plus flexible et puissante. 

Ce code VBA : 

• Identifie la dernière ligne et la dernière colonne utilisées. 
• Crée une plage nommée dynamique. 
• S’assure que la plage s’étend et se réduit automatiquement en fonction des données. 

Code VBA détaillé : 

Sub CreerPlageDynamique() 
    Dim ws As Worksheet 
    Dim derniereLigne As Long, derniereColonne As Long 
    Dim plage As Range 
    Dim nomPlage As String 
    ' Définir la feuille où créer la plage dynamique 
    Set ws = ThisWorkbook.Sheets("Feuil1") ' Modifier si nécessaire 
    ' Trouver la dernière ligne utilisée dans la colonne A (en supposant que les données commencent à A1) 
    derniereLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 
    ' Trouver la dernière colonne utilisée dans la ligne 1 (en supposant que les en-têtes commencent à A1) 
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique basée sur la dernière ligne et la dernière colonne trouvées 
    Set plage = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne)) 
    ' Définir le nom de la plage dynamique 
    nomPlage = "DonneesDynamiques" 
    ' Supprimer l'ancien nom de plage s'il existe 
    On Error Resume Next 
    ws.Names(nomPlage).Delete 
    On Error GoTo 0 
    ' Créer la nouvelle plage nommée dynamique 
    ws.Names.Add Name:=nomPlage, RefersTo:=plage 
    ' Message de confirmation 
    MsgBox "La plage dynamique '" & nomPlage & "' a été créée de " & _ 
           plage.Address(False, False), vbInformation, "Succès" 
End Sub

Explication détaillée : 

1. Définir la feuille de travail

Set ws = ThisWorkbook.Sheets("Feuil1") 

• Définit la feuille de travail « Feuil1 ». À modifier selon votre fichier. 

2. Trouver la dernière ligne avec des données

derniereLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 

• Recherche la dernière ligne non vide dans la colonne A. 
• ws.Rows.Count renvoie le nombre total de lignes (1 048 576 en Excel 2016+). 
• .End(xlUp) remonte à la première cellule remplie. 

3. Trouver la dernière colonne avec des données

derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 

• Recherche la dernière colonne non vide dans la ligne 1. 
• ws.Columns.Count renvoie le nombre total de colonnes (16 384 en Excel 2016+). 
• .End(xlToLeft) se déplace vers la gauche jusqu’à la première cellule remplie. 

4. Définir la plage dynamique

Set plage = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne)) 

• Crée une plage allant de A1 à la dernière ligne et colonne détectées. 

5. Nommer la plage dynamiquement

nomPlage = "DonneesDynamiques" 

• Attribue le nom de la plage dynamique (« DonneesDynamiques »). 

6. Supprimer l’ancien nom de plage s’il existe

On Error Resume Next 
ws.Names(nomPlage).Delete 
On Error GoTo 0

• Empêche les erreurs en supprimant l’ancien nom avant d’en créer un nouveau. 

7. Créer la plage nommée

ws.Names.Add Name:=nomPlage, RefersTo:=plage 

• Associe la plage trouvée à un nom de plage. 

8. Afficher un message de confirmation

MsgBox "La plage dynamique '" & nomPlage & "' a été créée de " & _ 
       plage.Address(False, False), vbInformation, "Succès"

• Affiche un message avec la plage détectée. 

Comment utiliser ce code ? 

1. Ouvrir Excel et appuyer sur ALT + F11 pour ouvrir l’éditeur VBA. 
2. Insérer un module (Insertion > Module). 
3. Copier-coller le code VBA. 
4. Modifier « Feuil1 » si nécessaire. 
5. Exécuter la macro (F5 ou Exécuter). 
6. Vérifier la plage dynamique (Formules > Gestionnaire de noms). 

Cas d’utilisation 

✅ Mise à jour automatique d’un tableau de bord lorsque des données sont ajoutées. 
✅ Sélection dynamique des données dans un tableau croisé dynamique. 
✅ Automatisation des rapports sans besoin de mise à jour manuelle.