Création et la mise à jour dynamique d’une plage de données, Excel VBA

Création et la mise à jour dynamique d’une plage de données, Excel VBA

Voici un code VBA détaillé pour la création et la mise à jour dynamique d’une plage de données dans Excel. Ce code permet à la plage de s’ajuster automatiquement en fonction des changements de données, tels que l’ajout ou la suppression de lignes.
Concept : Mise à jour dynamique de la plage de données
Une plage dynamique dans Excel fait référence à une plage qui s’ajuste automatiquement lorsque de nouvelles données sont ajoutées ou supprimées. Cela est très utile dans des tableaux de bord, des rapports ou des tableaux croisés dynamiques.
Code VBA pour Créer une Plage Dynamique
Ce macro VBA :
1. Identifie la dernière ligne et colonne d’un ensemble de données.
2. Définit une plage nommée de manière dynamique en utilisant cette dernière ligne et colonne.
3. Met à jour la plage nommée chaque fois que l’ensemble de données change.
Voici le code détaillé :

Sub MiseAJourPlageDynamique()
    Dim ws As Worksheet
    Dim derniereLigne As Long
    Dim derniereColonne As Long
    Dim plageDonnees As Range
    Dim nomFeuille As String
    Dim nomPlage As String
    ' Définir la feuille et le nom de la plage
    nomFeuille = "Feuille1" ' Modifiez avec le nom de votre feuille
    nomPlage = "PlageDynamique" ' Nom de la plage dynamique
    ' Référence à la feuille
    Set ws = ThisWorkbook.Sheets(nomFeuille)
    ' Trouver la dernière ligne utilisée dans la colonne A (ajustez selon votre jeu de données)
    derniereLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Trouver la dernière colonne utilisée dans la ligne 1
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set plageDonnees = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne))
    ' Appliquer le nom de la plage
    ws.Names.Add Name:=nomPlage, RefersTo:=plageDonnees
    ' Informer l'utilisateur
    MsgBox "La plage dynamique '" & nomPlage & "' a été mise à jour vers : " & _
           plageDonnees.Address, vbInformation, "Mise à jour réussie"
End Sub

Explication détaillée
1. Identifier la feuille et la plage nommée

• Le macro commence par définir la feuille de calcul (Feuille1) et la plage nommée (PlageDynamique).
• Vous pouvez bien sûr modifier ces valeurs en fonction de vos besoins.

2. Trouver la dernière ligne et colonne utilisées

• • La dernière ligne est déterminée avec la ligne suivante :

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

• • Cela recherche la dernière cellule non vide dans la colonne A (première colonne).
  • La dernière colonne est trouvée grâce à :

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

• Cela recherche la dernière cellule non vide dans la ligne 1.

3. Définir la plage dynamique

• • La plage est définie avec la ligne suivante :

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

• Cette commande capture toutes les cellules non vides, en partant de A1 jusqu’à la dernière ligne et colonne détectées.

4. Créer ou mettre à jour la plage nommée

• • Le code suivant permet de créer ou de mettre à jour la plage nommée "PlageDynamique" :

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

5. Afficher un message de confirmation

• • Une boîte de message s’affiche à la fin de l’exécution du code :

MsgBox "La plage dynamique '" & nomPlage & "' a été mise à jour vers : " & _

plageDonnees.Address, vbInformation, "Mise à jour réussie"

• Cela permet à l’utilisateur de savoir que la plage a été correctement mise à jour.

Cas d’utilisation

• Ce macro est particulièrement utile pour mettre à jour automatiquement les graphismes, les tableaux croisés dynamiques ou les validations de données.
• Plutôt que de mettre à jour manuellement la plage nommée lorsque de nouvelles données sont ajoutées, l’exécution de ce macro garantit que la plage sera toujours à jour.

Améliorations possibles
Pour automatiser la mise à jour de la plage à chaque modification de données, vous pouvez utiliser l’événement Worksheet_Change :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.UsedRange) Is Nothing Then
        Call MiseAJourPlageDynamique
    End If
End Sub

• Cela permet d’exécuter le macro automatiquement à chaque modification de données sur la feuille.