Créer une plage dynamique, Excel VBA

Créer une plage dynamique, Excel VBA

Voici un code VBA détaillé pour créer une plage dynamique dans Excel, accompagné d’une explication complète.
Code : Créer une Plage Dynamique dans Excel avec VBA

Sub CreateDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rng As Range
    Dim rngName As String
    ' Définir la feuille de travail
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Changez le nom de la feuille si nécessaire
    ' Trouver la dernière ligne utilisée dans la colonne A (ou toute autre colonne de référence)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Trouver la dernière colonne utilisée dans la ligne 1 (ou toute autre ligne de référence)
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Définir la plage dynamique
    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Nommer la plage dynamique
    rngName = "DynamicRange" ' Changez le nom si nécessaire
    ThisWorkbook.Names.Add Name:=rngName, RefersTo:=rng
    ' Confirmer l'opération
    MsgBox "Plage dynamique '" & rngName & "' créée avec succès !", vbInformation, "Succès"
    ' Libérer la mémoire
    Set rng = Nothing
    Set ws = Nothing
End Sub

Explication Détailée :
1. Définir la feuille de travail (ws)
Set ws = ThisWorkbook.Sheets("Sheet1")

• Cette ligne définit sur quelle feuille de travail le code VBA va agir. Modifiez "Sheet1" par le nom réel de la feuille.

2. Trouver la Dernière Ligne Utilisée (lastRow)
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• Cette ligne commence à partir de la dernière ligne de la colonne A et se déplace vers le haut pour trouver la dernière cellule non vide.
• Cette méthode est couramment utilisée pour détecter dynamiquement les limites des données.

3. Trouver la Dernière Colonne Utilisée (lastCol)
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• Cette ligne commence à partir de la dernière colonne de la ligne 1 et se déplace vers la gauche pour détecter la dernière colonne non vide.
• Cela permet d’inclure toutes les colonnes remplies dans la plage dynamique.

4. Définir la Plage Dynamique (rng)
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

• Cette ligne définit la plage dynamique qui commence à A1 (ligne 1, colonne 1) et s’étend jusqu’à la dernière ligne et colonne détectées.

5. Nommer la Plage Dynamique (rngName)
rngName = "DynamicRange"
ThisWorkbook.Names.Add Name:=rngName, RefersTo:=rng

• Cette partie assigne un nom (DynamicRange) à la plage dynamique, ce qui permet de l’utiliser facilement dans des formules, des graphiques ou des tableaux croisés dynamiques.

6. Afficher un Message de Confirmation
MsgBox "Plage dynamique '" & rngName & "' créée avec succès !", vbInformation, "Succès"

• Cela affiche un message pour informer l’utilisateur que la plage dynamique a été créée avec succès.

7. Libérer la Mémoire (Set ... = Nothing)
Set rng = Nothing
Set ws = Nothing

• Cette étape libère la mémoire après l’exécution du code, ce qui est une bonne pratique en VBA pour éviter les conflits et optimiser la performance.

Comment Utiliser Ce Code :
1. Ouvrez Excel et appuyez sur ALT + F11 pour ouvrir l’éditeur VBA.
2. Cliquez sur Insertion > Module.
3. Copiez et collez le code VBA dans le module.
4. Exécutez la macro CreateDynamicRange.
5. La plage dynamique sera créée et vous pourrez l’utiliser dans des formules comme :
=SOMME(DynamicRange)
6. Vous pouvez également vérifier le nom défini via Formules > Gestionnaire de noms.
Cas d’Utilisation :

• Tableaux Croisés Dynamiques Dynamiques
  Mettez à jour automatiquement les tableaux croisés dynamiques lorsque de nouvelles données sont ajoutées.
• Graphiques avec Données Auto-Extensibles
  Les plages dynamiques permettent aux graphiques de s’ajuster automatiquement sans avoir à mettre à jour manuellement les plages de données.
• Formules qui S’ajustent avec l’Ajout de Données
  Les plages nommées simplifient les calculs complexes en permettant une mise à jour automatique des plages.