Gestion des plages de cellules

Il est fréquemment nécessaire de gérer des plages de cellules, et il est nécessaire d'être efficace pour avoir une code VBA rapide et simple à comprendre.

Admettons ce tableau Excel :

 
A
B
C
D
E
1
         
2
  Prénoms
Janvier
Février
Mars
3
  Marcel
3
3
7
4
  Louis
4
1
9
5
  René
7
4
9
6
  TOTAUX
=SOMME(C3:C5)
=SOMME(D3:D5)
=SOMME(E3:E5)
7
         
8
         
9
         
10
  Prénoms
Avril
Mai
 
11
  Marcel
4
8
 
12
  Louis
2
5
 
13
  René
1
3
 
14
  TOTAUX
=SOMME(C11:C13)
=SOMME(D11:D13)
 
15
         

Nous allons utiliser CurrentRegion pour sélectionner les tableaux en entier. Constatez qu'il y a deux tableaux distincts, car séparés par 3 lignes vides.

Si on utilise n'importe laquelle des cellules entre B2 et E6, CurrentRegion nous renverra les mêmes résultats. Je rappelle que CurrentRegion désigne le tableau, et qu'on peut y accéder par la combinaison de touches CTRL * (Cliquez dans C5 par exemple, et appuyez sur CTRL * pour vous en assurer - Utilisez le * de votre pavé numérique, sinon c'est CTRL SHIFT 3)

Si on exécute cette commande :
Range("D4").CurrentRegion.Select
Excel va sélectionner B2:E6.

Attention :
Range("A7").CurrentRegion.Select
C'est la plage A2:E7 qui va être sélectionnée ! En effet, si on est sur une cellule qui touche le tableau, le tableau est sélectionné PLUS la ligne et/ou la colonne qui contient la cellule dans laquelle on était pour exécuter la macro !

Par contre, comme on pourrait s'y attendre :
Range("A8").CurrentRegion.Select
Ne sélectionne que A8, exactement comme
Range("A8").Select

On est pas obligé de sélectionner le CurrentRegion : On peut très bien le traiter "à distance" :
MsgBox Range("C2").CurrentRegion.Address
Va afficher
$B$2:$E$6

On peut connaître les limites de notre tableau :
MsgBox Range("E2").End(xlDown).Address
Affiche :
$B$6

La propriété End Accepte 4 paramètres possibles : xlDown, xlUp, xlToLeft et xlToRight

Attention : cette instruction Range("E2").CurrentRegion.End(xlDown)revient à sélectionner tout le tableau de B2 à E6, et d'appuyer sur CTRL - Flèche en bas. MAIS... si par malheur la cellule B2 ne contenait PAS le mot "Prénoms", mais qu'elle eut été vide, alors..
MsgBox Range("E2").End(xlDown).Address

Aurait renvoyé ... $B$3... A cause de cette satanée cellule vide ! En fait VBA fonctionne comme si vous aviez travaillé "A la main". Essayez :

  1. Effacez le contenu de la cellule B2
  2. Cliquez n'importe ou dans le tableau
  3. Appuyez sur CTRL *
  4. Appuyez sur CTRL Flèche en bas

Vous êtes effectivement sur B3.

Pour simplifier la suite de ce didacticiel, admettons que cette cellulke soit remplie, histoire d'avoir des tableaux bien carrés (ou rectangulaires...) ce qui est le plus souvent le cas lors d'une utilisation courante d'Excel.

Voici l'adresse de la cellule tout en bas à droite de notre tableau ($E$6) :
MsgBox Range("E2").End(xlDown).End(xlToRight).Address
Vous avez compris ? Dans la même instruction on va tout en bas puis tout à droite...

Et voici bien évidemment la cellule en haut à gauche :
MsgBox Range("D4").End(xlUp).End(xlToLeft).Address

Et attention : J'ai pris une cellule plus centrale que E2... ou plutôt une cellule qui ne se trouve pas à la première ligne, car si on est sur la ligne du haut de notre tableau et qu'on fait End(xlUp) - soit CTRL Flèche en haut, il ne reste pas sur la première ligne, mais il remonte au dessus du tableau... Ou plutôt il remonte jusqu'é ce qu'il ne puisse plus remonter (c'est notre cas : ligne 1), ou alors il remonte jusque sur la dernière ligne du tableau précédent :
MsgBox Range("D10").End(xlUp).End(xlToLeft).Address
Renverrait ...$D$6 ! Eh oui !

On peut maintenant connaître le nombre de lignes et de colonnes de notre tableau (on doit réutiliser notre bon vieux CurrentRegion):
MsgBox Range("D4").CurrentRegion.Columns.Count
4
MsgBox Range("D4").CurrentRegion.Rows.Count
5
Ainsi que le nombre de cellules :
MsgBox Range("D4").CurrentRegion.Cells.Count
20 (4 X 5 évidemment ...)

Et si nous avons besoin de connaître le numéro de la première et de la dernière ligne/colonne :
La colonne de gauche : (2)
Range("D4").End(xlToLeft).Column
La colonne de droite (5)
Range("D4").End(xlToRight).Column
La ligne du haut : (2)
Range("D4").End(xlUp).Row
La ligne du bas : (6)
Range("D4").End(xlDown).Row

Et si à la place du numéro de colonne, nous avons besoin de la lettre, il faut utiliser une astuce :
MsgBox Chr(Range("D4").End(xlToLeft).Column + 64)
Soit le code ASCII de la lettre B (66)

---