Cet article montrera comment créer une plage dynamique dans Excel VBA.
Déclarer une plage spécifique de cellules en tant que variable dans Excel VBA nous limite à travailler uniquement avec ces cellules particulières. En déclarant des plages dynamiques dans Excel, nous gagnons beaucoup plus de flexibilité sur notre code et les fonctionnalités qu'il peut exécuter.
Référencer des plages et des cellules
Lorsque nous référençons l'objet Range ou Cell dans Excel, nous y faisons normalement référence en codant en dur dans la ligne et les colonnes dont nous avons besoin.
Propriété de plage
En utilisant la propriété Range, dans les exemples de lignes de code ci-dessous, nous pouvons effectuer des actions sur cette plage, telles que changer la couleur des cellules ou mettre les cellules en gras.
| 12 | Range ("A1:A5").Font.Couleur = vbRedRange ("A1:A5").Font.Bold = True |
Propriété des cellules
De même, nous pouvons utiliser la propriété Cells pour faire référence à une plage de cellules en référençant directement la ligne et la colonne dans la propriété cells. La ligne doit toujours être un nombre mais la colonne peut être un nombre ou une lettre entre guillemets.
Par exemple, l'adresse de cellule A1 peut être référencée comme :
| 1 | Cellules(1,1) |
Ou
| 1 | Cellules (1, "A") |
Pour utiliser la propriété Cells pour référencer une plage de cellules, nous devons indiquer le début et la fin de la plage.
Par exemple, pour référencer la plage A1 : A6, nous pourrions utiliser cette syntaxe ci-dessous :
| 1 | Plage(Cellules(1,1), Cellules(1,6) |
Nous pouvons ensuite utiliser la propriété Cells pour effectuer des actions sur la plage selon les exemples de lignes de code ci-dessous :
| 12 | Range(Cells(2, 2), Cells(6, 2)).Font.Color = vbRedRange(Cells(2, 2), Cells(6, 2)).Font.Bold = True |
Plages dynamiques avec variables
Au fur et à mesure que la taille de nos données change dans Excel (c'est-à-dire que nous utilisons plus de lignes et de colonnes que les plages que nous avons codées), il serait utile que les plages auxquelles nous nous référons dans notre code changent également. En utilisant l'objet Range ci-dessus, nous pouvons créer des variables pour stocker le nombre maximal de lignes et de colonnes de la zone de la feuille de calcul Excel que nous utilisons, et utiliser ces variables pour ajuster dynamiquement l'objet Range pendant l'exécution du code.
Par exemple
| 1234 | Dim lRow comme entierDim lCol comme entierlRow = Range("A1048576").End(xlUp).RowlCol = Range("XFD1").End(xlToLeft).Column |
Dernière ligne de la colonne
Comme il y a 1048576 lignes dans une feuille de calcul, la variable lRow ira au bas de la feuille, puis utilisera la combinaison spéciale de la touche Fin plus la touche Flèche haut pour aller à la dernière ligne utilisée dans la feuille de calcul - cela nous donnera le numéro de la ligne dont nous avons besoin dans notre gamme.
Dernière colonne de la ligne
De même, le lCol se déplacera vers la colonne XFD qui est la dernière colonne d'une feuille de calcul, puis utilisera la combinaison de touches spéciale de la touche Fin plus la touche Flèche gauche pour aller à la dernière colonne utilisée dans la feuille de calcul - cela nous donnera le numéro de la colonne dont nous avons besoin dans notre gamme.
Par conséquent, pour obtenir toute la plage utilisée dans la feuille de calcul, nous pouvons exécuter le code suivant :
| 1234567891011 | Sous-GetRange()Dim lRow en tant qu'entierDim lCol en tant qu'entierDim rng As RangelRow = Range("A1048576").End(xlUp).Row'utiliser le lRow pour aider à trouver la dernière colonne de la plagelCol = Range("XFD" & lRow).End(xlToLeft).ColumnDéfinir rng = Range(Cells(1, 1), Cells(lRow, lCol))'msgbox pour nous montrer la gammeMsgBox "La plage est " & rng.AddressFin du sous-marin |
Cellules spéciales - LastCell
Nous pouvons également utiliser la méthode SpecialCells de l'objet Range pour obtenir la dernière ligne et la dernière colonne utilisées dans une feuille de calcul.
| 123456789101112 | Sub UseSpecialCells()Dim lRow en tant qu'entierDim lCol en tant qu'entierDim rng As RangeDim rngBegin As RangeDéfinir rngBegin = Range("A1")lRow = rngBegin.SpecialCells(xlCellTypeLastCell).RowlCol = rngBegin.SpecialCells(xlCellTypeLastCell).ColumnDéfinir rng = Range(Cells(1, 1), Cells(lRow, lCol))'msgbox pour nous montrer la gammeMsgBox "La plage est " & rng.AddressFin du sous-marin |
Plage d'occasion
La méthode de plage utilisée inclut toutes les cellules qui contiennent des valeurs dans la feuille de calcul actuelle.
| 123456 | Sub UsedRangeExample()Dim rng As RangeDéfinir rng = ActiveSheet.UsedRange'msgbox pour nous montrer la gammeMsgBox "La plage est " & rng.AddressFin du sous-marin |
Région actuelle
La région actuelle diffère de UsedRange en ce qu'elle examine les cellules entourant une cellule que nous avons déclarée comme plage de départ (c'est-à-dire la variable rngBegin dans l'exemple ci-dessous), puis examine toutes les cellules « attachées » ou associées à cette cellule déclarée. Si une cellule vide dans une ligne ou une colonne apparaît, la CurrentRegion cessera de rechercher d'autres cellules.
| 12345678 | Sous-région actuelle()Dim rng As RangeDim rngBegin As RangeDéfinir rngBegin = Range("A1")Définir rng = rngBegin.CurrentRegion'msgbox pour nous montrer la gammeMsgBox "La plage est " & rng.AddressFin du sous-marin |
Si nous utilisons cette méthode, nous devons nous assurer que toutes les cellules de la plage dont vous avez besoin sont connectées sans lignes ou colonnes vides entre elles.
Plage nommée
Nous pouvons également référencer des plages nommées dans notre code. Les plages nommées peuvent être dynamiques dans la mesure où lorsque des données sont mises à jour ou insérées, le nom de plage peut changer pour inclure les nouvelles données.
Cet exemple va changer la police en gras pour le nom de plage « Janvier »
| 12345 | Exemple de nom de sous-plage()Dim rng comme plageDéfinir rng = Range ("Janvier")rng.Font.Bold = = TrueFin du sous-marin |
Comme vous le verrez dans l'image ci-dessous, si une ligne est ajoutée au nom de la plage, le nom de la plage se met automatiquement à jour pour inclure cette ligne.
Si nous réexécutions ensuite l'exemple de code, la plage affectée par le code serait C5:C9 alors que dans le premier cas, elle aurait été C5:C8.
les tables
Nous pouvons référencer des tables (cliquez pour plus d'informations sur la création et la manipulation de tables en VBA) dans notre code. Lorsqu'une donnée de table dans Excel est mise à jour ou modifiée, le code qui fait référence à la table fera alors référence aux données de table mises à jour. Ceci est particulièrement utile lorsque vous faites référence à des tableaux croisés dynamiques connectés à une source de données externe.
En utilisant ce tableau dans notre code, nous pouvons faire référence aux colonnes du tableau par les en-têtes de chaque colonne et effectuer des actions sur la colonne en fonction de leur nom. Au fur et à mesure que les lignes du tableau augmentent ou diminuent en fonction des données, la plage du tableau s'ajustera en conséquence et notre code fonctionnera toujours pour toute la colonne du tableau.
Par exemple:
| 123 | Sous DeleteTableColumn()ActiveWorkbook.Worksheets("Feuille1").ListObjects("Table1").ListColumns("Fournisseur").DeleteFin du sous-marin |
