Création d’une plage dynamique liée à l’innovation, Excel VBA

Création d’une plage dynamique liée à l’innovation, Excel VBA

Voici un guide détaillé sur la création d’une plage dynamique dans Excel en utilisant VBA. Cela permet de définir une plage qui s’ajuste automatiquement à mesure que vous ajoutez ou supprimez des données d’une feuille de calcul. 

Objectif 

Le but est de créer une plage dynamique qui : 

• Se met à jour automatiquement lorsque des lignes ou des colonnes sont ajoutées ou supprimées. 
• Peut être utilisée pour des tâches comme l’actualisation de plages dans des tableaux ou des graphiques. 

Code VBA pour créer une plage dynamique 

Voici un exemple de code VBA pour créer une plage dynamique : 

Sub CreerPlageDynamique() 
    Dim ws As Worksheet 
    Dim derniereLigne As Long 
    Dim derniereColonne As Long 
    Dim plageDynamique As Range 
    ' Définir la feuille de calcul active 
    Set ws = ActiveSheet 
    ' Trouver la dernière ligne utilisée dans la colonne A 
    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 en fonction de la dernière ligne et colonne 
    Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne))
    ' Afficher la plage dynamique dans un message 
    MsgBox "Plage dynamique : " & plageDynamique.Address 
    ' Optionnel : Sélectionner la plage et créer un tableau 
    plageDynamique.Select 
    ws.ListObjects.Add(xlSrcRange, plageDynamique, 0, xlYes, , xlNone).Name = "MonTableauDynamique" 
End Sub

Explication du Code 

1. Référence à la feuille de calcul : 

• La variable ws fait référence à la feuille de calcul active. Vous pouvez remplacer cela par Set ws = ThisWorkbook.Sheets(« NomDeLaFeuille ») si vous voulez cibler une feuille spécifique. 

2. Trouver la dernière ligne et la dernière colonne : 

• derniereLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row : Cela permet de trouver la dernière ligne utilisée dans la colonne A. Le code va partir de la dernière ligne possible (1048576 pour Excel) et remonter jusqu’à la première cellule utilisée. 
• derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column : Cela trouve la dernière colonne utilisée dans la première ligne, en remontant de la dernière colonne vers la première.

3. Définir la plage dynamique : 

• Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne)) : Cette ligne définit la plage dynamique, de la cellule A1 jusqu’à la cellule située à l’intersection de la dernière ligne et de la dernière colonne utilisée.

4. Affichage de la plage dynamique : 

• MsgBox "Plage dynamique : " & plageDynamique.Address : Un message s’affiche avec l’adresse de la plage dynamique, ce qui vous permet de voir immédiatement quelle plage est définie. 

5. Création d’un tableau à partir de la plage dynamique (optionnel) : 

• Le code sélectionne ensuite cette plage dynamique et crée un tableau avec ws.ListObjects.Add. Cela transforme la plage en un tableau Excel que vous pouvez manipuler facilement. 

Étapes pour exécuter le code 

1. Ouvrez l’éditeur VBA en appuyant sur Alt + F11. 
2. Dans l’éditeur, allez dans Insertion > Module et collez le code dans ce module. 
3. Fermez l’éditeur et exécutez la macro en appuyant sur Alt + F8, sélectionnez CreerPlageDynamique, puis cliquez sur Exécuter. 

Avantages des plages dynamiques avec VBA 

1. Adaptabilité : La plage s’ajuste automatiquement lorsque des données sont ajoutées ou supprimées. 
2. Automatisation : Cela permet d’éviter les erreurs humaines lors de la mise à jour des plages. 
3. Facilité d’utilisation : Vous pouvez facilement travailler avec ces plages dans des opérations comme la création de tableaux ou l’analyse des données. 

Étape 4 : Personnalisation et extensions 

Vous pouvez utiliser ce code comme point de départ pour des cas d’utilisation spécifiques : 

Exemple 1 : Mise à jour de la source d’un tableau croisé dynamique 

Si vous souhaitez mettre à jour la source d’un tableau croisé dynamique avec une plage dynamique, vous pouvez utiliser ce code : 

Sub MettreAJourSourceTCD() 
    Dim ws As Worksheet 
    Dim derniereLigne As Long 
    Dim derniereColonne As Long 
    Dim tableauCroise As PivotTable 
    Dim plageDynamique As Range 
    ' Définir la feuille de calcul et le tableau croisé dynamique 
    Set ws = ThisWorkbook.Sheets("Feuille1") 
    Set tableauCroise = ws.PivotTables("TableauCroise1") ' Remplacez par le nom de votre TCD 
    ' Trouver la dernière ligne et colonne des données 
    derniereLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique 
    Set plageDynamique = ws.Range(ws.Cells(1, 1), ws.Cells(derniereLigne, derniereColonne)) 
    ' Mettre à jour la source du tableau croisé dynamique 
    tableauCroise.ChangePivotCache ws.PivotTableWizard(PivotCache:=ThisWorkbook.PivotTableCaches.Create(xlDatabase, plageDynamique)) 
End Sub 

Cela met à jour la source des données pour un tableau croisé dynamique, garantissant qu’il utilise toujours la plage dynamique définie. 

Conclusion 

L’utilisation de plages dynamiques dans VBA est un moyen puissant et flexible de gérer des données qui changent fréquemment dans Excel. Vous pouvez facilement les appliquer à des tableaux, graphiques, ou tableaux croisés dynamiques pour automatiser et rendre vos processus plus efficaces.