"FAQ : VBA [Toutes versions]- Détection de cellule vide Ligne/colonne"
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.
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é.
