VBA : ajouter une valeur dans la dernière ligne vide

L’identification de la dernière ligne utilisée dans une feuille Excel représente l’une des tâches les plus fréquentes en programmation VBA. Cette opération constitue le fondement de nombreuses automatisations, qu’il s’agisse d’ajouter de nouvelles données, de calculer des plages dynamiques ou de traiter des informations existantes.

Variables et références de feuille

ws est une variable de type Worksheet. Cette ligne définit la variable ws pour faire référence à la feuille de calcul nommée « Feuil1 ». Dim derniereLigne As Long suivi de derniereLigne = ActiveSheet.UsedRange.Rows.Count vous donnera directement le nombre total de lignes utilisées. Le choix du type Long évite des bugs difficiles à diagnostiquer, en particulier sur des fichiers partagés où la taille des données peut évoluer rapidement.

Méthode .End(xlUp) : principe et fiabilité

La syntaxe Cells(Rows.Count, 1).End(xlUp).Row simule l’action de se positionner en bas de la colonne A et d’appuyer sur Ctrl+Flèche Haut. .End(xlUp) : Cela équivaut à appuyer sur Ctrl + ↑ dans Excel. Cette commande part du bas de la feuille (dernière ligne) et remonte jusqu’à ce qu’elle trouve une cellule non vide. xlUp est généralement plus fiable que xlDown. Si des lignes vides se trouvent après la dernière ligne avec des données, l’utilisation de xlDown donnerait des résultats incorrects, car il considérerait la première ligne vide comme « la dernière ligne ». Cette approche garantit une détection précise même en présence de cellules vides intercalaires dans votre dataset.

Pour obtenir la première ligne vide disponible, il suffit d’ajouter 1 au résultat : derniereLigne = Cells(Rows.Count, 1).End(xlUp).Row + 1. Si vous préférez travailler avec la lettre de la colonne plutôt que son index numérique, la syntaxe est tout aussi simple : derniereLigne = Range("A" & Rows.Count).End(xlUp).Row.

UsedRange et CurrentRegion : avantages et limites

La propriété UsedRange représente la zone rectangulaire qui englobe toutes les cellules utilisées d’une feuille de calcul. L’avantage principal de cette méthode réside dans sa rapidité d’exécution pour les grandes feuilles de calcul. Elle évite le parcours cellule par cellule et fournit instantanément les dimensions de la zone de travail. Cependant, cette approche présente une limitation importante : elle compte depuis la première ligne utilisée, pas nécessairement depuis la ligne 1.

Lire aussi: Avantages Subvention Mutuelle

Lorsque vos données sont organisées sous forme de bloc contigu (sans lignes ni colonnes complètement vides à l’intérieur), la propriété CurrentRegion devient une alliée précieuse. Elle renvoie la zone rectangulaire entourant une cellule, délimitée par les premières lignes et colonnes vides rencontrées. Cette méthode est particulièrement adaptée aux tableaux « propres », par exemple les listes d’export CRM ou les bases de données structurées sans lignes vides.

SpecialCells(xlCellTypeLastCell) et nettoyage

Une autre méthode native consiste à utiliser SpecialCells(xlCellTypeLastCell), qui renvoie la dernière cellule considérée comme utilisée par Excel sur la feuille. Pour fiabiliser l’usage de SpecialCells(xlCellTypeLastCell), on recommande généralement de nettoyer la feuille (suppression des lignes superflues, réinitialisation des formats) puis d’enregistrer le classeur. Vous évitez ainsi qu’Excel conserve en mémoire des cellules « fantômes ».

Cas des formules renvoyant "" et cellules « visibles »

Mon problème vient du fait que je n'arrive pas à sélectionner la dernière ligne de ma feuille. La raison est que mon fichier Excel est rempli de formules, du type SI(A2="";"";A2). Cela implique que j'ai des cellules qui sont en apparence vides, mais qui ont une formule. Une erreur fréquente consiste à considérer qu’une cellule dont la formule renvoie "" est vide. Or, pour VBA, cette cellule n’est pas vraiment vide : elle contient une formule, même si le résultat affiché semble vide. IsEmpty renvoie True uniquement si la cellule n’a jamais reçu de valeur, formule ou autre. Dès qu’une formule est présente, même si elle renvoie une chaîne vide, IsEmpty renverra False.

Vous pouvez aussi vous appuyer sur WorksheetFunction.CountA qui considère comme non vides les cellules contenant des formules renvoyant une chaîne vide, selon le comportement souhaité. Si vous devez identifier la dernière ligne non vide d’une colonne précise, sans passer par End(xlUp), vous pouvez utiliser la fonction de feuille NBVAL, accessible en VBA via Application.WorksheetFunction.CountA. Ici, si vos données commencent en A1 et qu’il y a 534 cellules non vides, derniereLigne prendra la valeur 534. Cette approche est intéressante lorsque votre colonne ne contient pas de trous (pas de cellules vides au milieu).

Recherche avancée : Find et boucles personnalisées

La méthode Find de l’objet Range est souvent utilisée pour rechercher une valeur spécifique, mais elle peut aussi servir à localiser la dernière cellule non vide d’une feuille ou d’une colonne. Ici, nous cherchons n’importe quel contenu ("*") dans la colonne A, en analysant les formules (xlFormulas) et en parcourant les lignes dans le sens inverse. La première cellule trouvée sera donc la dernière cellule non vide de la colonne. Vous pouvez étendre cette technique à toute la feuille en remplaçant Columns("A") par Cells.

Au-delà des méthodes natives les plus connues, vous pouvez exploiter en VBA Excel toute la richesse des objets Range et Worksheet pour affiner la détection de la dernière ligne utilisée. La boucle For Each permet de parcourir une collection de cellules pour analyser finement leur contenu. Imaginons que vous souhaitiez trouver la dernière ligne contenant une valeur numérique dans la colonne A, en ignorant les valeurs textuelles. Ici, nous parcourons chaque cellule de la colonne A, et nous mettons à jour derniereLigne uniquement lorsque la cellule contient une valeur numérique.

Filtres, lignes masquées et visibilité

Les filtres automatiques et les lignes masquées peuvent également compliquer l’identification de la dernière ligne en VBA Excel. Les méthodes classiques comme End(xlUp) ou UsedRange ne tiennent pas compte de l’état visible ou masqué des cellules : elles travaillent sur l’ensemble de la feuille, filtrée ou non. Pour cela, vous pouvez utiliser SpecialCells(xlCellTypeVisible) combiné à Rows ou Areas. Ici, nous récupérons la dernière zone visible, puis la dernière ligne de cette zone, ce qui revient à identifier la dernière ligne réellement affichée à l’écran.

Performance et bonnes pratiques

Pour améliorer les performances, vous pouvez limiter la plage scannée à un sous-ensemble réaliste plutôt qu’à toute la colonne. Par exemple, si vous savez que vos données ne dépassent jamais la ligne 10 000, inutile de parcourir les 1 048 576 lignes disponibles. Le premier réflexe consiste à privilégier les méthodes « vectorielles » plutôt que les boucles cellule par cellule. Par exemple, Cells(Rows.Count, col).End(xlUp) ou Find sont extrêmement rapides car elles s’appuient sur le moteur interne d’Excel.

Sur de gros classeurs, le pattern suivant évite que chaque modification de cellule ne déclenche un rafraîchissement de l’écran ou un recalcul complet des formules : Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' ... Ce pattern évite que chaque modification de cellule ne déclenche un rafraîchissement de l’écran ou un recalcul complet des formules. Sur de gros classeurs, le gain peut se mesurer en secondes, voire en minutes.

Enfin, limitez autant que possible les accès répétés à la feuille. Si vous devez analyser toute une colonne pour déterminer la dernière ligne selon un critère complexe, chargez d’abord la plage dans un tableau VBA, traitez ce tableau en mémoire, puis ne revenez vers la feuille que pour écrire le résultat. En combinant ces bonnes pratiques avec une sélection judicieuse des méthodes de détection de la dernière ligne, vous obtenez des macros capables de gérer des volumes de données importants sans sacrifier la réactivité.

VBA-06 Identifier la dernière ligne ou colonne d'un tableau

Ajouter une valeur après la dernière ligne non vide

Sinon, il faudrait mieux copier sur la 1ere cellule vide après la dernière ligne non vide, non ? Pour ajouter une valeur, calculez la première ligne vide comme Cells(Rows.Count, col).End(xlUp).Row + 1 puis écrivez directement la valeur : Cells(ligneVide, col).Value = "Nouvelle valeur". Cette façon de faire vous permet d’encapsuler la détection de la dernière ligne dans une procédure Sub dédiée à l’ajout de données. Dans vos formulaires utilisateur ou boutons de commande, vous n’aurez plus qu’à rassembler les valeurs des contrôles dans un tableau et à appeler AjouterContact.

Trier une sélection par une colonne spécifique

Et 2e petite question si vous permettez: une fois la sélection faite, comment faire pour trier les données de Z à A selon la colonne 9 ? Une fois la plage déterminée dynamiquement (par exemple Range("A2:J" & derniereLigne)), vous pouvez appliquer la méthode Sort en spécifiant Key:=Range("I2") (colonne 9), Order:=xlDescending. Vous récupérez alors une plage triée de Z à A selon la colonne 9.

Débogage et vérification

Cette ligne affiche la valeur de dernièreLigne dans la fenêtre Immediate (accessible avec Ctrl + G). Cela est utile pour le débogage et pour vérifier le résultat. Cette ligne sélectionne toute la dernière ligne contenant des données. Une autre façon de trouver la dernière ligne est d’utiliser la propriété UsedRange, qui donne la plage de cellules ayant déjà contenu des données. La méthode de recherche de la dernière ligne avec .End(xlUp) est la plus fiable, surtout lorsque vous travaillez avec des données qui peuvent ne pas remplir toutes les lignes de manière continue.

Conseils pratiques

Privilégiez le typage Long pour les variables de lignes et colonnes.
Préférez End(xlUp) ou Find plutôt que des boucles lorsque c’est possible.
Utilisez Application.ScreenUpdating = False et Application.Calculation = xlCalculationManual pour optimiser les opérations sur de grands jeux de données.
Chargez les plages dans un tableau VBA pour des traitements complexes hors feuille.
Testez vos macros sur des copies avant exécution sur les fichiers de production.

Bonjour à tous,Je cherche à faire une macro VBA, qui sélectionne une plage de données sur Excel avec 10 colonnes mais un nombre de lignes variables. Mon problème vient du fait que je n'arrive pas à sélectionner la dernière ligne de ma feuille. J'ai essayé avec le classique "Cells(Rows.Count, 2).End(xlUp).Row", mais cela ne fonctionne pas pour moi. Je vous remercie par avance pour votre aide!

balises: