Créer une plage dynamique avec précision, Excel VBA

Créer une plage dynamique avec précision, Excel VBA

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

Objectif : 

Créer une plage dynamique qui s’ajuste automatiquement lorsque des données sont ajoutées ou supprimées dans une feuille de calcul, garantissant que la plage reste précise et correctement définie pour être utilisée dans diverses opérations (par exemple, graphiques, tableaux croisés dynamiques, ou autres manipulations de données). 

Code : 

Sub CreateDynamicRange() 
    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim lastCol As Long 
    Dim dynamicRange As Range 
    Dim rangeName As String 
    ' Définir la feuille de calcul 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ' Trouver la dernière ligne et la dernière colonne avec des données 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique (en notation A1) 
    Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) 
    ' Définir le nom de la plage dynamique 
    rangeName = "DynamicRange" 
    ' Créer la plage dynamique en utilisant la propriété Name 
    ThisWorkbook.Names.Add Name:=rangeName, RefersTo:=dynamicRange 
    ' Afficher un message de confirmation 
    MsgBox "La plage dynamique '" & rangeName & "' a été créée avec succès !", vbInformation 
End Sub

Explication détaillée : 

1. Définir la feuille de calcul : 

Set ws = ThisWorkbook.Sheets("Sheet1") 

• Cette ligne définit la feuille de calcul sur laquelle la plage dynamique sera créée. Remplacez « Sheet1 » par le nom de la feuille que vous souhaitez utiliser. 

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

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• lastRow : Cette ligne détermine la dernière ligne avec des données dans la colonne A (en supposant qu’il n’y ait pas de lacunes dans les données). Elle commence à partir du bas de la feuille (ws.Rows.Count) et remonte vers le haut (xlUp). 
• lastCol : De manière similaire, cette ligne trouve la dernière colonne dans la première ligne contenant des données. Elle commence à partir de la colonne la plus à droite (ws.Columns.Count) et se déplace vers la gauche (xlToLeft). 

3. Créer la plage dynamique : 

Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) 

• Cette ligne définit la plage dynamique depuis la cellule A1 jusqu’à la dernière ligne et colonne utilisée. La plage dynamique s’ajustera automatiquement au fur et à mesure que vous ajoutez ou supprimez des données. 

4. Nommer la plage : 

ThisWorkbook.Names.Add Name:=rangeName, RefersTo:=dynamicRange 

• Cela ajoute la plage dynamique au gestionnaire de noms du classeur. La variable rangeName est définie sur « DynamicRange », mais vous pouvez la changer selon vos besoins. 

5. Message de confirmation : 

MsgBox "La plage dynamique '" & rangeName & "' a été créée avec succès !", vbInformation 

• Un message de confirmation s’affiche pour signaler que la plage dynamique a bien été créée. 

Points clés : 

• La plage dynamique s’ajuste automatiquement pour inclure les données de la feuille de calcul. Cela est particulièrement utile lorsque vous travaillez avec des ensembles de données de taille variable. 
• La plage dynamique n’est pas statique, ce qui signifie que, lorsque vous ajoutez ou supprimez des lignes ou des colonnes, la plage s’adapte automatiquement. 
• La propriété RefersTo est ce qui rend la plage dynamique. Cela permet à la plage de s’étendre ou de se rétrécir en fonction du nombre de lignes et de colonnes contenant des données. 

Cas d’utilisation : 

Vous pouvez utiliser cette plage dynamique dans vos formules, graphiques ou tableaux croisés dynamiques pour toujours faire référence au jeu de données le plus à jour sans avoir à ajuster manuellement les plages. Par exemple, si vous utilisez une plage dynamique dans un graphique, ce dernier se mettra à jour automatiquement dès que les données de la plage changent. 

Ce code peut être adapté à différentes feuilles ou colonnes, et vous pouvez également le modifier pour trouver la dernière ligne ou colonne en fonction d’une colonne ou d’une ligne spécifique, plutôt que simplement la colonne A et la ligne 1.