VBA: trouver la dernière cellule utilisée


La recherche de la dernière cellule utilisée (ou la dernière cellule non-vide si vous préférez) dans une Feuille Excel est une des opérations incontournables dans VBA. L'information sur la dernière cellule utilisée est indispensable notamment pour la bonne performance des macros – elle permet de limiter le nombre d'itérations (loops) au minimum nécessaire, de limiter la taille des plages à copier ou à formater, etc.. Elle permet donc d'augmenter de manière significative la performance des macros VBA.

VBA: trouver la dernière cellule utilisée
Ce tutoriel va vous montrer comment trouver cette dernière cellule grâce au VBA ainsi que les différents types de la "dernière cellule utilisée". Car cette notion n'est pas aussi simple que l'on pourrait croire…

Notion de la "dernière cellule utilisée"

Aussi étonnant que cela puisse paraître, l'expression "dernière cellule utilisée" peut avoir nombreux interprétation. Et c'est justement l'interprétation de "dernière cellule utilisée" qui déterminera le code VBA à utiliser. Il est donc important de déterminer correctement le type de cellule que l'on cherche.

Voici les interprétations principales de l'expression "dernière cellule utilisée":

  • la toute dernière cellule de la Feuille qui est utilisée – c'est à dire contient une valeur ou une formule
  • la dernière cellule de la Feuille qui contient une valeur (les formules qui donnent le résultat égal à "" ne sont pas prises en compte)
  • la dernière cellule utilisée dans une colonne précise (par exemple: la dernière cellule utilisée dans la colonne "C")
  • la dernière cellule utilisée dans une ligne précise (par exemple: dans la ligne 85)

VBA: trouver la dernière cellule utilisée

Le code VBA décliné en plusieurs versions dans cette section trouve la dernière cellule utilisée – qu'elle contienne une valeur ou une formule (et peu importe le résultat de cette formule). Pour construire ce code, nous allons faire appel à la méthode .Find (car il ne s'agit pas d'une fonction VBA mais bien d'une Method).

DerniereCellule_Ligne = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
DerniereCellule_Colonne = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
DerniereCellule_AdresseAbsolue = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Address
DerniereCellule_Adresse = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Address(RowAbsolute:=False, ColumnAbsolute:=False)

 

VBA: trouver la dernière cellule contenant une valeur

À la différence de la façon décrite plus haut pour trouver la toute dernière cellule utilisée, la recherche suivante permet de trouver la dernière cellule qui contient une valeur (différente de ""). Donc, cette recherche NE tient PAS compte des formules. Elle tient compte uniquement des cellules non-vides du point de vue de la valeur. Ceci peux être pratique pour limiter le nombre des itérations dans votre code ou si vous voulez, par exemple, copier les valeurs d'un endroit à un autre.

Ainsi, vous pouvez copier ou parcourir uniquement les cellules "utiles" tout en utilisant les formules au-delà de la dernière cellule avec une valeur. Vous garder donc la flexibilité par rapport aux données avec lesquelles vous travaillez.

Le code de cette recherche a la même structure que le code plus haut car il utilise également la méthode .Find. Ce qui change, c'est le contenu de l'argument LookIn qui est ici xlValues.

DerniereCellule_Ligne = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
DerniereCellule_Colonne = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
DerniereCellule_AdresseAbsolue = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Address
DerniereCellule_Adresse = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Address(RowAbsolute:=False, ColumnAbsolute:=False)

 

La dernière ligne utilisée dans une colonne donnée

Si vous avez besoin de trouver la dernière cellule utilisée dans une colonne précise, voici deux manières de procéder:

DerniereLigneUtilisee = Range("X" & Rows.Count).End(xlUp).Row 'où X est la colonne donnée

ou, deuxième manière de faire:

DerniereLigneUtilisee = Cells(Rows.Count, X).End(xlUp).Row 'où X est le numéro de la colonne donnée (ex. 3 pour la colonne "C")

Ces deux instructions donneront le même résultat – vous pouvez donc choisir selon votre besoin ou vos préférences. Cela dépend si vous utiliser la lettre (on utilisera le première instruction) ou le numéro de la colonne (on utilisera alors la deuxième instruction).

Dans de nombreux forums sur VBA et Excel, vous trouverez souvent cette instruction sous la forme suivante:

DerniereLigneUtilisee = Range("A65536").End(xlUp).Row

Mais cette approche peut poser le problème de compatibilité car ainsi écrite, cette instruction utilise un nombre déterminé des lignes: et comme les différentes versions d'Excel ont un nombre différent de lignes, vous risquez d'obtenir des erreurs, des résultats incorrects et vous serez obligés de modifier cette instruction cas par cas.

Au contraire, l'instruction que je vous propose, c'est-à-dire:

DerniereLigneUtilisee = Range("A" & Rows.Count).End(xlUp).Row

utilisera toujours le nombre correct des lignes et est donc tout à fait indépendante de la version d'Excel utilisée – elle donnera le résultat correct dans tous les cas.

Il en va de même pour le nombre des colonnes.

Attention – si aucune cellule dans la colonne choisie n'est utilisée, le résultat final sera 1 (et non 0)!

La dernière colonne utilisée dans une ligne donnée

Cette manière de trouver la dernière cellule utilisée dans une ligne fonctionne de la même manière que le code précédent (pour la dernière ligne dans une colonne).

DerniereColonneUtilisee = Cells(X, Columns.Count).End(xlToLeft).Column 'où X est le numéro de la ligne

'exemple (pour la dernière cellule utilisée dans la ligne 156):
MaDerniereColonneUtilisee = Cells(156, Columns.Count).End(xlToLeft).Column

Attention – tout comme pour la recherche de la dernière ligne d'une colonne donnée, si aucune cellule dans la ligne choisie n'est utilisée, le résultat final sera 1 (et non 0)!

Remarque: grâce à la méthode .Find, il est possible de chercher également les cellules contenant un commentaire. Pour cela, l'argument LookIn doit contenir la constante xlComments.

Voici un exemple d'utilisation:

DerniereCelluleAvecCommentaire = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlComments).Address

Si vous cherchez plus d'informations sur la méthode .Find, vous pouvez consulter: Méthode Find (Microsoft)

Conclusion de ce tutoriel

Maintenant, vous avez à votre disposition le code VBA nécessaire à trouver les dernières cellules utilisées ou les dernières cellules non-vides où qu'elles se trouvent (Feuille, colonne, ligne). De plus, vous pouvez choisir parmi les différentes possibilités le code qui vous convient au mieux concernant la notion de la dernière cellule.

De plus, tous ces codes sont des codes d'une ligne dont il est très simple de les utiliser dans le code d'un Projet VBA sans impacté son lisibilité…

Pour aller plus loin en VBA

Pour terminer, je vous propose quelques autres articles et tutoriels qui pourraient vous servir dans votre travail en VBA…

77 0

Laissez un commentaire

Votre adresse e-mail ne sera pas publiée.

10 commentaires sur “VBA: trouver la dernière cellule utilisée”