Comment remplir une colonne... en VBA

Dans mon premier billet sur ce blog (le 23 octobre dernier), j’avais parlé de cette manipulation courante qui consiste à recopier jusqu’au bas d’une colonne ce qu’on a saisi dans la première cellule tout en haut : une valeur fixe pour toute la table ou bien une formule par exemple. J’avais exposé des raccourcis qui permettent de faire ça rapidement au clavier puis j’avais conclu en annonçant que j’aborderais plus tard cette manip-là sous un angle VBA. C’est ce que je fais aujourd’hui !

Une fois qu’on a sous la main une macro qui fait ça, remplir une colonne va très vite. Et pour peu qu’on déclenche cette macro par une très brève séquence de touches, ça ressemble à ce mouvement du jeu Tétris : la chute rapide d’une pièce jusqu’au plancher, chute qu’on déclenche en appuyant sur la flèche du bas.

D’où le nom TétrisFillDown que j’ai donné à cette macro...


L’idée est simple :
1. repérer la cellule du haut,
2. repérer la cellule du bas,
3. couvrir la plage reliant ces deux cellules et y effectuer un remplissage vers le bas.




Les variables dont on va avoir besoin pour faire ça sont :
- des objets Range pour identifier la cellule du haut (TopCell) et celle du bas (BottomCell),
- des entiers pour identifier une ligne et une colonne (i et j),
- un autre objet Range (c) pour parcourir des cellules, on verra plus loin pourquoi.


Repérons d’abord la cellule du haut. On pourrait se contenter de prendre systématiquement comme référence la cellule active. Mais ce n’est pas très confortable, selon la manière dont on travaille :
- on veut souvent faire ce remplissage juste après avoir saisi une valeur ou une formule dans la première ligne. Or si on a validé cette saisie par la touche Entrée, la nouvelle cellule active n’est plus la bonne, il faudrait d’abord y retourner. Cela dit, il y a des chances qu’on soit toujours dans la bonne colonne : la validation entraine en général le déplacement de la cellule active une ligne plus bas dans la même colonne (c’est le réglage par défaut d’Excel).
- autre cas de figure : comme on travaille sur cette colonne-là, on s’est peut-être déplacé plus bas dans le tableau pour une raison ou une autre, et la cellule à prendre en référence n’est même plus celle juste au-dessus de la cellule active... mais malgré tout on est toujours dans la bonne colonne !

La question décisive est en fait de savoir si la cellule active est vide ou non. Repérer la cellule du haut pour notre manip consiste donc à :
- si la cellule active n’est pas vide, prendre cette cellule active ;
- sinon, prendre la première cellule non-vide vers le haut, qu’on identifiera avec la propriété End.
En VBA :
      If Not IsEmpty(ActiveCell) Then

Set TopCell = ActiveCell

Else

Set TopCell = ActiveCell.End(xlUp)

      End If


Pour la cellule du bas, on va définir séparément son numéro de ligne et son numéro de colonne.

Sa ligne est la même que celle de la dernière cellule de la région courante. Comme la région courante de la cellule active est donnée par

ActiveCell.CurrentRegion

et la dernière cellule d’une plage par

SpecialCells(xlCellTypeLastCell)

le numéro de cette ligne-là est :
      i = ActiveCell.CurrentRegion.SpecialCells(xlCellTypeLastCell).Row

Sa colonne, c’est facile, c’est celle de la cellule active :
      j = ActiveCell.Column

La cellule du bas est donc définie comme ceci :
      Set BottomCell = Cells(i, j)


Et voilà, il ne reste plus qu’à couvrir et remplir :
      Range(TopCell, BottomCell).FillDown


Mais avant d’assembler tout ça, deux petits éléments de finition.

Le premier, c’est l’idée de faire plusieurs remplissages d’un seul coup.

Situation : sur plusieurs colonnes successives, on entre des valeurs ou des fomules qui enrichissent la table : on aura donc plusieurs colonnes à remplir jusqu’en bas !

Au lieu d’exécuter la macro plusieurs fois, sur chaque colonne l’une après l’autre, on va plutôt faire une boucle sur les cellules de la plage sélectionnée, et exécuter ce code à chaque fois. Il suffira donc de sélectionner une plage couvrant l’ensemble des colonnes à remplir, et d’exécuter la macro une seule fois. En gros, on va faire plutôt ça :

C’est là l’intérêt de la variable Range déclarée plus haut et appelée c : au lieu de travailler sur ActiveCell, on va utiliser cette variable-là et faire la manip dans chacune des colonnes de la plage sélectionnée. Ce n’est pas utile de parcourir l’intégralité de la plage de cellules sélectionnée : il suffit de parcourir les cellules de la première ligne de cette plage :
For Each c In Selection.Rows(1)
''' remplissage de la colonne contenant la cellule “c”
Next c

Le second, c’est cette amélioration-accélération facile qui consiste à désactiver le rafraichissement écran, en manipulant la propriété booléenne Application.ScreenUpdating au début et à la fin du code.



Voilà, maintenant on peut mettre tout ça en musique !


Sub TétrisFillDown()

Application.ScreenUpdating = False

Dim c As Range, TopCell As Range, BottomCell As Range
Dim i as Long, j as Long

' boucle (nécessaire si plusieurs colonnes à remplir !)
For Each c In Selection.Rows(1)
  
      ' définition de la cellule supérieure
      If Not IsEmpty(c) Then

Set TopCell = c

Else

Set TopCell = c.End(xlUp)

      End If
      
      ' définition de la cellule inférieure
      i = c.CurrentRegion.SpecialCells(xlCellTypeLastCell).Row
      j = c.Column
      Set BottomCell = Cells(i, j)
      
      ' remplissage
      Range(TopCell, BottomCell).FillDown
  
Next c
  
Application.ScreenUpdating = True

End Sub