Créer et supprimer une plage dynamique, Excel VBA

Créer et supprimer une plage dynamique, Excel VBA

Voici un code VBA détaillé pour créer et supprimer une plage dynamique dans Excel, ainsi qu’une explication complète sur son fonctionnement. 

Code VBA pour créer et supprimer une plage dynamique : 

Sub CreateAndDeleteDynamicRange() 
    Dim ws As Worksheet 
    Dim dynamicRange As Range 
    Dim lastRow As Long 
    Dim lastColumn As Long 
    Dim rangeName As String 
    ' Définir la feuille de travail avec laquelle vous travaillez 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ' Trouver la dernière ligne et la dernière colonne avec des données dans la feuille de travail 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Dernière ligne dans la colonne A 
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ' Dernière colonne dans la ligne 1 
    ' Définir la plage dynamique 
    Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)) 
    ' Nommer la plage dynamique (optionnel) 
    rangeName = "DynamicRange" 
    On Error Resume Next ' Au cas où le nom de la plage existe déjà 
    ws.Names(rangeName).Delete ' Supprimer la plage nommée existante 
    On Error GoTo 0 ' Revenir à la gestion des erreurs standard 
    ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange 
    ' Afficher un message indiquant que la plage a été créée 
    MsgBox "La plage dynamique '" & rangeName & "' a été créée de A1 à " & _ 
        ws.Cells(lastRow, lastColumn).Address 
    ' Maintenant, supprimer la plage dynamique 
    ws.Names(rangeName).Delete 
    MsgBox "La plage dynamique '" & rangeName & "' a été supprimée." 
End Sub

Explication du Code : 

1. Définition de la feuille de travail (ws) : Set ws = ThisWorkbook.Sheets("Sheet1") 

Cette ligne définit l’objet feuille de travail (ws) pour faire référence à la feuille nommée « Sheet1 ». Vous pouvez la modifier pour désigner n’importe quelle feuille de votre classeur. 

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

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

• lastRow : Cette ligne trouve la dernière ligne avec des données dans la colonne A. Elle part du bas de la feuille de travail (ws.Rows.Count) et utilise xlUp pour remonter jusqu’à la première cellule non vide. 
• lastColumn : De même, cette ligne trouve la dernière colonne avec des données en partant de la dernière colonne de la ligne 1 et se déplaçant vers la gauche grâce à xlToLeft. 

3. Définir la plage dynamique : Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))

La plage dynamique est définie de la cellule A1 à la cellule déterminée par lastRow et lastColumn. Cela crée une plage flexible qui s’adapte aux données. 

4. Nommer la plage dynamique : 

rangeName = "DynamicRange" 
On Error Resume Next 
ws.Names(rangeName).Delete 
On Error GoTo 0 
ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange

• La plage est nommée (« DynamicRange ») afin de faciliter son utilisation dans des formules ou d’autres parties du code. 
• Le code commence par vérifier si le nom de la plage existe déjà (On Error Resume Next désactive temporairement la gestion des erreurs). Si ce nom existe, il supprime la plage précédente (ws.Names(rangeName).Delete). 
• Ensuite, il crée une nouvelle plage nommée (ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange). 

5. Supprimer la plage dynamique : ws.Names(rangeName).Delete 

Enfin, le code supprime la plage dynamique qui a été précédemment nommée. Un message de confirmation est affiché à l’utilisateur pour indiquer que la plage a été supprimée. 

Comment ça fonctionne : 

• Le code commence par calculer la taille du jeu de données en trouvant la dernière ligne et la dernière colonne avec des données. 
• Ensuite, il définit une plage dynamique allant de A1 à la dernière cellule utilisée. 
• La plage est ensuite nommée pour faciliter son utilisation dans des formules Excel ou d’autres parties du classeur. 
• Après avoir effectué des actions avec la plage, la plage nommée est supprimée pour nettoyer la feuille. 

Cas d’utilisation : 

Ce script VBA est utile dans des situations où : 

• Vous avez un jeu de données qui peut changer de taille, et vous devez créer une plage qui s’adapte dynamiquement. 
• Vous voulez créer une plage dynamique nommée pour l’utiliser dans des formules Excel, puis la supprimer une fois qu’elle n’est plus nécessaire.