Créer et gérer des plages dynamiques, Excel VBA

Créer et gérer des plages dynamiques, Excel VBA

Voici un code VBA détaillé pour créer et gérer des plages dynamiques dans Excel, ainsi qu’une explication complète.
Code VBA pour Créer un Support de Plage Dynamique

Option Explicit
' Cette procédure crée une plage nommée dynamique basée sur les données d'une colonne spécifiée.
Sub CreateDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rngName As String
    Dim rngAddress As String
    Dim dynamicRange As Range
    ' Définir la feuille de travail où se trouve la plage
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modifiez le nom de votre feuille ici
    ' Définir le nom de la plage
    rngName = "DynamicDataRange" ' Modifiez le nom de la plage selon vos besoins
    ' Trouver la dernière ligne avec des données dans la colonne A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Définir l'adresse de la plage dynamique (en supposant la colonne A)
    rngAddress = ws.Range("A2:A" & lastRow).Address(True, True, xlA1, True)
    ' Définir l'objet de la plage dynamique
    Set dynamicRange = ws.Range("A2:A" & lastRow)
    ' Créer ou mettre à jour la plage nommée dans le classeur
    On Error Resume Next
    ThisWorkbook.Names(rngName).Delete ' Supprimer le nom existant s'il existe
    On Error GoTo 0
    ' Ajouter une nouvelle plage nommée
    ThisWorkbook.Names.Add Name:=rngName, RefersTo:=dynamicRange
    ' Avertir l'utilisateur
    MsgBox "Plage dynamique '" & rngName & "' créée avec succès à " & rngAddress, vbInformation, "Plage Dynamique Créée"
End Sub

Explication Détaillee du Code
1. Déclaration des Variables

Dim ws As Worksheet
Dim lastRow As Long
Dim rngName As String
Dim rngAddress As String
Dim dynamicRange As Range

• ws : Représente la feuille de calcul où les données sont situées.
• lastRow : Stocke le numéro de la dernière ligne contenant des données dans la colonne A.
• rngName : Contient le nom de la plage dynamique.
• rngAddress : Contient l’adresse de la plage dynamique.
• dynamicRange : Représente l’objet de la plage elle-même.

2. Définir la Référence à la Feuille de Calcul
Set ws = ThisWorkbook.Sheets("Sheet1") ' Modifiez le nom de votre feuille ici

• Nous attribuons à la variable ws la référence de la feuille « Sheet1 ».
• Changez "Sheet1" pour correspondre au nom réel de votre feuille.

3. Définir le Nom de la Plage
rngName = "DynamicDataRange"

• Cette ligne définit le nom de la plage dynamique.
• Vous pouvez changer "DynamicDataRange" par n’importe quel autre nom de votre choix.

4. Trouver la Dernière Ligne avec des Données
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• Cette ligne trouve la dernière ligne non vide dans la colonne A.
• ws.Cells(ws.Rows.Count, 1).End(xlUp).Row :
• ws.Rows.Count renvoie le nombre total de lignes dans la feuille.
• .End(xlUp) remonte à partir de la dernière ligne pour trouver la dernière cellule avec des données.

5. Définir l’Adresse de la Plage Dynamique
rngAddress = ws.Range("A2:A" & lastRow).Address(True, True, xlA1, True)

• ws.Range("A2:A" & lastRow) : Crée une plage allant de A2 jusqu’à la dernière ligne contenant des données.
• .Address(True, True, xlA1, True) : Convertit la plage en une adresse absolue au format A1.

6. Assigner la Plage à un Objet
Set dynamicRange = ws.Range("A2:A" & lastRow)

• Cette ligne assigne la plage identifiée à la variable dynamicRange.

7. Gérer les Plages Nommées Existantes

On Error Resume Next
ThisWorkbook.Names(rngName).Delete ' Supprimer le nom existant s'il existe
On Error GoTo 0

• On Error Resume Next : Empêche les erreurs si le nom de la plage n’existe pas déjà.
• ThisWorkbook.Names(rngName).Delete : Supprime un nom de plage existant si trouvé.
• On Error GoTo 0 : Rétablit la gestion des erreurs normale.

8. Créer la Plage Nommée
ThisWorkbook.Names.Add Name:=rngName, RefersTo:=dynamicRange

• Cette ligne ajoute une nouvelle plage nommée au classeur.
• La plage est maintenant dynamique et se met à jour automatiquement lorsque les données changent.

9. Afficher un Message de Confirmation

MsgBox "Plage dynamique '" & rngName & "' créée avec succès à " & rngAddress, vbInformation, "Plage Dynamique Créée"

• Une boîte de message s’affiche pour informer l’utilisateur que la plage dynamique a été créée avec succès.

Comment Utiliser le Code
1. Ouvrez Excel et appuyez sur ALT + F11 pour ouvrir l’éditeur VBA.
2. Insérez un nouveau module (Insertion > Module).
3. Copiez et collez le code dans le module.
4. Modifiez "Sheet1" et "DynamicDataRange" si nécessaire.
5. Exécutez CreateDynamicRange en appuyant sur F5 ou en l’assignant à un bouton.
Avantages de Cette Méthode
✅ La plage se met à jour automatiquement lorsque de nouvelles données sont ajoutées.
✅ Évite d’utiliser des fonctions volatiles comme OFFSET dans les noms définis.
✅ Offre une méthode claire et facile à maintenir pour travailler avec des données dynamiques.
✅ Compatible avec les formules, les graphiques et les tableaux croisés dynamiques.