Créer une plage dynamique robuste, Excel VBA

Créer une plage dynamique robuste, Excel VBA

Créer une plage dynamique en VBA Excel est une tâche courante lorsque vous travaillez avec des ensembles de données qui peuvent changer en taille ou en contenu. En VBA, cela peut être fait en écrivant un code qui ajuste dynamiquement la plage en fonction du nombre de lignes ou de colonnes remplies avec des données. Pour rendre le code robuste, il est essentiel de tenir compte des problèmes potentiels, tels que des cellules vides, des types de données mélangés et des données non contiguës. 

Voici un exemple détaillé de la façon de créer une plage dynamique avec VBA, accompagné d’une explication détaillée : 

Code VBA : Créer une Plage Dynamique 

Sub CreateDynamicRange() 
    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim lastColumn As Long 
    Dim dynamicRange As Range 
    ' Définir l'objet feuille de calcul 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ' Trouver la dernière ligne utilisée dans la feuille de calcul (en supposant que les données commencent à partir de la ligne 1) 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    ' Trouver la dernière colonne utilisée dans la feuille de calcul (en supposant que les données commencent à partir de la colonne A) 
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la plage dynamique (de A1 à la cellule utilisée en bas à droite) 
    Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)) 
    ' Exemple : Sélectionner la plage dynamique 
    dynamicRange.Select 
    ' Exemple : Mettre en surbrillance la plage dynamique 
    dynamicRange.Interior.Color = RGB(255, 255, 0) 
    ' Exemple : Afficher l'adresse de la plage dynamique dans la fenêtre immédiate 
    Debug.Print "Adresse de la plage dynamique : " & dynamicRange.Address 
End Sub

Explication du Code : 

1. Définir la Feuille de Calcul : 
La première partie du code assigne la feuille de calcul sur laquelle la plage dynamique doit être créée. Dans ce cas, nous travaillons avec Sheet1, mais vous pouvez la modifier pour travailler avec n’importe quelle feuille. 

Set ws = ThisWorkbook.Sheets("Sheet1") 

2. Trouver la Dernière Ligne et la Dernière Colonne : 
Pour rendre la plage dynamique, il est nécessaire de déterminer la dernière ligne et la dernière colonne utilisées dans les données. 

• lastRow: Cela trouve la dernière ligne de la colonne A contenant des données en utilisant xlUp (en recherchant de bas en haut). Cela est utile si l’ensemble de données contient des espaces vides au milieu. 
• lastColumn: Cela trouve la dernière colonne de la ligne 1 contenant des données en utilisant xlToLeft. 

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

3. Créer la Plage : 
La plage dynamique est ensuite définie en fixant les points de départ et de fin en fonction de la dernière ligne et colonne trouvées. Cela garantit que seule la partie pertinente des données est sélectionnée. 

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

Exemples d’Utilisation de la Plage Dynamique : 

• Sélectionner la Plage : dynamicRange.Select permet de sélectionner la plage dynamique pour effectuer d’autres opérations. 
• Mettre en Surbrillance la Plage : La plage est mise en surbrillance avec une couleur jaune en utilisant Interior.Color. 
• Imprimer l’Adresse de la Plage : Vous pouvez également utiliser Debug.Print pour afficher l’adresse de la plage dynamique dans la fenêtre immédiate de VBA pour vérifier ou déboguer. 

dynamicRange.Select 
dynamicRange.Interior.Color = RGB(255, 255, 0) 
Debug.Print "Adresse de la plage dynamique : " & dynamicRange.Address

Considérations de Robustesse : 

Lors de la gestion de plages dynamiques, vous devez vous assurer que le code est suffisamment robuste pour gérer différents problèmes qui peuvent survenir, comme : 

1. Cellules Vides : Si certaines cellules sont vides dans l’ensemble de données, en particulier dans des colonnes ou des lignes qui sont autrement remplies, End(xlUp) ou End(xlToLeft) peuvent renvoyer des résultats incorrects. Pour gérer cela : 

Assurez-vous que la plage est correctement calculée en vérifiant plusieurs colonnes et lignes pour les données. 

2. Types de Données Mixtes : La plage peut contenir un mélange de nombres, de textes ou de formules, et vous devez le gérer correctement, surtout si des opérations telles que des calculs sont impliquées. 

3. Données Non Contiguës : Si les données ne sont pas contiguës (c’est-à-dire qu’il y a des espaces vides), vous devrez adapter le code pour identifier les blocs réels de données. 

4. Limites d’Excel : Excel a un nombre maximal de lignes et de colonnes (1048576 lignes et 16384 colonnes dans Excel 2016 et versions ultérieures). Assurez-vous que votre plage reste dans ces limites. 

Code Amélioré pour Gérer les Espaces et Plusieurs Colonnes : 

Si vous travaillez avec des données non contiguës ou que vous devez vérifier plusieurs colonnes pour des espaces vides, vous pouvez étendre la logique comme suit : 

Sub CreateEnhancedDynamicRange() 
    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim lastColumn As Long 
    Dim startColumn As Long 
    Dim dynamicRange As Range 
    Dim col As Long 
    Set ws = ThisWorkbook.Sheets("Sheet1") 
    ' Trouver la dernière ligne dans la colonne A 
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row *
    ' Trouver la dernière colonne utilisée dans la première ligne 
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 
    ' Définir la colonne de départ pour la plage (si ce n'est pas 1) 
    startColumn = 1 
    ' Boucle pour vérifier les colonnes et ajuster si nécessaire 
    For col = startColumn To lastColumn 
        If Application.WorksheetFunction.CountA(ws.Columns(col)) > 0 Then 
            Set dynamicRange = ws.Range(ws.Cells(1, col), ws.Cells(lastRow, col)) 
            dynamicRange.Select 
        End If 
    Next col 
End Sub

Conclusion : 

Créer une plage dynamique en VBA Excel consiste à déterminer la dernière ligne et la dernière colonne utilisées, puis à construire une plage en fonction de ces points. L’élément clé pour rendre le code robuste est de prendre en compte les cellules vides, les données non contiguës et les types de données mixtes. Cela permet d’adapter le code à une variété de scénarios de données, garantissant que votre plage s’adapte toujours aux changements dans votre ensemble de données.