VBA Plage Dynamique

Cet article explique 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 obtenons beaucoup plus de flexibilité sur notre code et la fonctionnalité qu’il peut exécuter.

Référencement des Plages et des Cellules

Lorsque nous référençons l’objet Range ou Cells dans Excel, nous nous y référons normalement en codant en dur les lignes et les colonnes dont nous avons besoin.

Propriété Range

En utilisant la propriété Range, dans l’exemple de lignes de code ci-dessous, nous pouvons effectuer des actions sur cette plage, comme changer la couleur des cellules ou mettre les cellules en gras.

Range("A1:A5").Font.Color = vbRed
Range("A1:A5").Font.Bold = True

Propriété Cells

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 la cellule A1 peut être référencée comme suit :

Cells(1,1)

Ou

Cells(1, "A")

Pour utiliser la propriété Cells afin de 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 la syntaxe suivante :

Range(Cells(1,1), Cells(1,6)

Nous pouvons ensuite utiliser la propriété Cells pour effectuer des actions sur la plage, comme le montrent les lignes de code ci-dessous :

Range(Cells(2, 2), Cells(6, 2)).Font.Color = vbRed
Range(Cells(2, 2), Cells(6, 2)).Font.Bold = True

Plages Dynamiques avec Variables

Comme 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 les numéros de ligne et de colonne maximum 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

Dim lRow as integer
Dim lCol as integer
lRow  = Range("A1048576").End(xlUp).Row
lCol  = 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 et utilisera la combinaison spéciale de la touche Fin et de la touche Flèche vers le haut pour aller à la dernière ligne utilisée dans la feuille de calcul – ce qui nous donnera le numéro de la ligne dont nous avons besoin dans notre plage.

Dernière Colonne de la Ligne

De même, il est possible de se déplacer jusqu’à la colonne XFD, qui est la dernière colonne d’une feuille de calcul, puis d’utiliser la combinaison spéciale des touches Fin et Flèche gauche pour atteindre la dernière colonne utilisée dans la feuille de calcul – ce qui nous donne le numéro de la colonne dont nous avons besoin dans notre plage.

Par conséquent, pour obtenir l’ensemble de la plage utilisée dans la feuille de calcul, nous pouvons exécuter le code suivant :

Sub ObtenirPlage()
  Dim lRow As Integer
  Dim lCol As Integer
  Dim rng As Range
  lRow = Range("A1048576").End(xlUp).Row
' utilise la variable lRow pour trouver la dernière colonne de la plage
  lCol = Range("XFD" & lRow).End(xlToLeft).Column
  Set rng = Range(Cells(1, 1), Cells(lRow, lCol))
' la plage résultante est affichée dans une boite de message
  MsgBox "La plage est " & rng.Address
End Sub

SpecialCells – 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.

Sub ExempleSpecialCells()
  Dim lRow As Integer
  Dim lCol As Integer
  Dim rng As Range
  Dim rngBegin As Range
  Set rngBegin = Range("A1")
  lRow = rngBegin.SpecialCells(xlCellTypeLastCell).Row
  lCol = rngBegin.SpecialCells(xlCellTypeLastCell).Column
  Set rng = Range(Cells(1, 1), Cells(lRow, lCol))
' la plage résultante est affichée dans une boite de message
  MsgBox "La plage est " & rng.Address
End Sub

UsedRange

La méthode UsedRange comprend toutes les cellules contenant des valeurs dans la feuille de calcul actuelle.

Sub ExempleUsedRange()
  Dim rng As Range
  Set rng = ActiveSheet.UsedRange
' la plage résultante est affichée dans une boite de message
  MsgBox "La plage est " & rng.Address
End Sub

CurrentRegion

L’objet CurrentRegion diffère de la plage utilisée en ce sens 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 toutes les cellules qui sont « attachées » ou associées à cette cellule déclarée. Si une cellule vide apparaît dans une ligne ou une colonne, la CurrentRegion cesse de chercher d’autres cellules.

Sub ExempleCurrentRegion()
  Dim rng As Range
  Dim rngBegin As Range
  Set rngBegin = Range("A1")
  Set rng = rngBegin.CurrentRegion
' la plage résultante est affichée dans une boite de message
  MsgBox "La plage est " & rng.Address
End Sub

Si nous utilisons cette méthode, nous devons nous assurer que toutes les cellules de la plage souhaitée sont connectées et qu’il n’y a pas de lignes ou de colonnes vides entre elles.

Plage Nommée

Nous pouvons également faire référence à 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 la plage peut être modifié pour inclure les nouvelles données.

Dans cet exemple, la police de caractères sera mise en gras pour le nom de la plage « Janvier »

Sub ExemplePlageNommee()
  Dim rng as Range 
  Set rng = Range("Janvier")
  rng.Font.Bold = = True 
End Sub

Comme vous pouvez le voir dans l’image ci-dessous, si une ligne est ajoutée au nom de la plage, le nom de la plage est automatiquement mis à jour pour inclure cette ligne.

Si nous exécutons à nouveau le code de l’exemple, la plage affectée par le code sera C5:C9 alors que dans le premier cas, il s’agissait de C5:C8.

Tableaux

Nous pouvons faire référence à des tableaux (cliquez pour plus d’informations sur la création et la manipulation de tableaux en VBA) dans notre code. Lorsque les données d’un tableau dans Excel sont mises à jour ou modifiées, le code qui se réfère au tableau se réfère alors aux données mises à jour du tableau. Cette fonction est particulièrement utile lorsqu’il s’agit de tableaux croisés dynamiques connectés à une source de données externe.

En utilisant ce tableau dans notre code, nous pouvons nous référer aux colonnes du tableau par les titres de chaque colonne et effectuer des actions sur la colonne en fonction de leur nom. Lorsque les lignes du tableau augmentent ou diminuent en fonction des données, la plage du tableau s’ajuste en conséquence et notre code fonctionne toujours pour l’ensemble des colonnes du tableau.

En voici un exemple :

Sub SupprimerColonneTableau()
  ActiveWorkbook.Worksheets("Feuil1").ListObjects("Table1").ListColumns("Supplier").Delete
End Sub