Posez votre question »

VBA [Toutes versions]- Détection de cellule vide Ligne/colonne

Août 2015




Introduction

Entre les versions 2003 et 2007, le nombre de lignes que peut atteindre une feuille Excel a considérablement évolué (de 65536 à 1048576). De ce fait, les codes VBA tels que :
  • versions < 2007 :
    Dim DernLigne As Long 
    DernLigne = Range("A65536").End(xlUp).Row
  • depuis 2007 :
    Dim DernLigne As Long 
    DernLigne = Range("A1048576").End(xlUp).Row

empêchent la portabilité de votre classeur d'une version vers l'autre.

Voici plusieurs méthodes permettant de définir la première cellule vide.

1ère ligne vide d'une colonne

Il est souvent bien utile de savoir qu'elle est la première ligne vide d'une colonne, notamment pour pouvoir coller des données sous des données déjà existantes.
Les différents codes donnés ici sont valables quelque soit votre version d'Excel.

Par une boucle

Nous allons, dans cet exemple, boucler sur toutes les cellules de la colonne C afin de déterminer la première cellule vide rencontrée.
Dim Lig As Long
Lig = 1 'première ligne à vérifier
Do While Not IsEmpty(Range("C" & Lig))
    Lig = Lig + 1
Loop
MsgBox "La première ligne vide colonne C est la ligne : " & Lig


Remarques : Cette procédure comporte des inconvénients, notamment la vitesse d'exécution. En effet, sur de très grands tableaux, la boucle sur toutes les lignes est la plus lente de toutes les possibilités. A titre d'information et pour relativiser, sur un tableau de 60 000 lignes elle a duré 1,05 secondes sur mon PC.
Le second problème réside dans le cas ou vous auriez, dans cette colonne, des cellules vides. La procédure s'arrêtera sur la première cellule vide rencontrée et vous renverra le numéro de la ligne correspondante.
Pas toujours très pratique.

Combinaison Ctrl+Flèche du bas

Vous l'aurez remarqué, lorsque vous tapez, [depuis votre feuille excel, après avoir sélectionné la première cellule de votre colonne (C1 dans l'exemple)] simultanément sur les touches Ctrl et flèche du bas, la cellule sélectionnée devient la dernière cellule non vide de votre colonne. Cette manipulation de touches peut être réalisée sous forme d'un code VBA, sans passer par les SendKeys. Il s'agit ici de la propriété End d'Excel, à laquelle on attribue le paramètre obligatoire de direction xlDown.
Cette façon de procéder nous renvoyant le numéro de la dernière ligne non-vide de la colonne, pour obtenir la première ligne vide, il suffira de lui ajouter 1...

Dim DLig As Long 
DLig = Range("C1").End(xlDown).Row + 1


Remarque : Si vous avez une (ou des) cellule vide au milieu de votre colonne, c'est la ligne de celle-ci qui sera retournée par votre fonction...

Combinaison Ctrl+Flèche du haut

A l'inverse de la combinaison précédente, lorsque vous sélectionnez la dernière cellule de votre colonne (C65536 ou C1048576 selon votre version) et que vous tapez simultanément sur Ctrl+touche du haut, vous allez sélectionner la dernière cellule non vide de votre colonne.
Le code VBA correspondant, indépendant de votre version serait, en pseudo code :
Range("C" nombre_de_ligne_total_de_ma_feuille).End(xlUp).Row
Or, le nombre de ligne total d'une feuille quelque soit la version est exprimé, en VBA par : Rows.Count (compte de lignes).
D'où le code suivant :

Dim maLigne As Long 
maLigne = Range("C" & Rows.Count).End(xlUp).Row + 1


Remarque : Cette méthode, lorsque votre colonne est entièrement vide, retourne la valeur 2. Or, dans la réalité, la première ligne vide est la 1.
Pour pallier ceci, il conviendra de faire un test sur la première cellule de votre colonne. Du genre :
If Range("C1") <> "" Then
    maLigne = Range("C" & Rows.Count).End(xlUp).Row + 1
Else
  maLigne = 1
End If

La méthode find

La méthode find permet de chercher une valeur dans une plage de cellules. On peut lui adjoindre des paramètres tels que le sens et/ou la direction de la recherche (complément d'informations). La méthode find renvoie la première occurrence qu'elle trouve. Il nous suffit donc de chercher :
  • quoi : "n'importe quoi",
  • ou : "colonne(3)" (= colonne C),
  • Direction : "par colonnes"
  • sens : "vers le haut" (= précédent)

En fait, nous allons ici reproduire la méthode End(xlUp) grâce à Find, en cherchant la première cellule "remplie" de notre colonne.

Ce qui nous donne, en VBA :

Dim Ligne As Long
Ligne = Columns(3).Find("*", , , , xlByColumns, xlPrevious).Row + 1


Remarques : Comme les manières de faire précédentes, il convient d'ajouter 1 pour obtenir le numéro de la première ligne vide.
Cette méthode renvoie une erreur lorsque la colonne est entièrement vide. Dans ce cas, utilisez un test sur la première cellule :
If Range("C1") <> "" Then
    Ligne = Columns(3).Find("*", , , , xlByColumns, xlPrevious).Row + 1
Else
  Ligne = 1
End If


Le côté positif de celle-ci est que vous utilisez le numéro de la colonne plutôt que la lettre. cela rends les boucles plus aisées et plus facilement compréhensible.

Exemple d'un code qui boucle sur les colonnes A à J pour déterminer laquelle est la plus "longue" :
Dim Lign As Long, numCol As Integer, tempLig As Long, Colonne As String
For numCol = 1 to 10 'colonnes A à J
  If Cells(1, numCol) <> "" Then 'test si 1ère ligne vide
      Ligne = Columns(numCol).Find("*", , , , xlByColumns, xlPrevious).Row
      If Ligne > tempLig Then tempLig = Ligne: Colonne = Chr(64 + numCol)
  End If
Next
MsgBox "La colonne la plus remplie est : " & Colonne


On l'a vu, cette méthode est une écriture différente de End(xlUp). On notera donc que la même possibilité est possible pour End(xlDown) à l'aide de la syntaxe suivante :
Ligne = Columns(3).Find("", , , , xlByColumns, xlNext).Row

Nota : Plus besoin de +1, on cible directement la première cellule vide.
Cette façon de faire comporte le même inconvénient que End(xlDown), à savoir qu'une cellule vide au milieu de notre colonne renvoie un résultat erroné.

La méthode SpecialCells

Cette méthode permet de trouver la dernière ligne de votre feuille, qu'il y ait ou non des lignes vides.
Remarque : Attention toutefois, en cas de suppression de lignes, pour récupérer le réel numéro de dernière ligne, il vous faudra enregistrer le classeur.

Dim Ligne As Long
Ligne = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row


On le voit ici, inutile de référencer un Range ou une Colonne. Il s'agit de la dernière ligne absolue de la feuille.
Cette méthode comporte également une propriété Address, utile pour repérer également la colonne :
Dim Adresse As String
Adresse = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address

Dernière colonne non-vide d'une ligne

Vous serez également confrontés un jour au même problème en ce qui concerne les colonnes. En effet, nous ne connaissons pas toujours, à l'avance, le nombre de colonnes utilisées dans notre feuille. Afin de réaliser des boucles sur les colonnes, cette information peut s'avérer indispensable.

Les exemples sont tous basés sur le fait de chercher la dernière colonne remplie de la ligne 4. Par contre, contrairement au chapitre précédent, nous nous attacherons à retourner le numéro de la dernière colonne remplie, pas la première colonne vide...

Les explications seront moins fournies que pour les lignes, parce que c'est exactement la même chose.

Voici quelques unes des méthodes possibles.

Columns.Count & End(xlToLeft)

La méthode End comporte un paramètre de direction comme nous l'avons vu. Cette direction peut très bien être "vers la gauche". A ce moment, il nous suffit de partir de la dernière colonne de notre classeur (Columns.Count) et de revenir vers la gauche. L'équivalent de la combinaison de touches Ctrl+Flèche gauche.

Dim DernCol As Integer 
DernCol = Cells(4, Cells.Columns.Count).End(xlToLeft).Column


Nota : Plus besoin d'une variable de type Long. Ici le type Integer suffit largement.

End(xlToRight)

Dim DernCol As Integer 
DernCol = Range("A4").End(xlToRight).Column

Attention en cas de cellule vide dans la ligne 4...

La méthode SpecialCells

Dim DernCol As Integer 
DernCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

La méthode Find

Dim Col As Integer
Col = Rows(3).Find("*", , , , xlByRows, xlPrevious).Column

ou :
Dim Col As Integer
Col = Rows(3).Find("", , , , xlByRows, xlNext).Column

Attention aux cellules vides...

Conclusion

Sachez qu'il existe d'autres façons de procéder. J'ai essayé ici de décrire le maximum de situations que vous pourriez rencontrer. Si cela ne s'avérait insuffisant, n'hésitez pas à créer un sujet dans le forum approprié.

Pour une lecture illimitée hors ligne, vous avez la possibilité de télécharger gratuitement cet article au format PDF :
Vba-toutes-versions-detection-de-cellule-vide-ligne-colonne.pdf

Réalisé sous la direction de , fondateur de CommentCaMarche.net.

A voir également

Dans la même catégorie

VBA - Last Non-empty row [All versions]
Par jak58 le 25 juillet 2011
VBA: Ultima fila que contiene datos [Cualquier versión]
Par Carlos-vialfa le 13 juillet 2011
Publié par pijaku.
Ce document intitulé «  VBA [Toutes versions]- Détection de cellule vide Ligne/colonne  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.