Pierre Fauconnier

Salut.

On est souvent amené à rechercher la dernière ligne ou la dernière colonne d'une feuille Excel qui contient des données, bref la dernière cellule remplie d'une feuille, car trouver la dernière cellule utilisée d'une feuille donnerait les deux informations. Voici une fonction à placer dans votre module xlTools (Vous avez bien un module xlTools, hein? ). Elle renvoie la dernière cellule réellement utilisée d'une feuille (celle qui, au moment de l'appel de la fonction, est la dernière "occupée" de la feuille). Si aucune cellule n'a encore été utilisée, la cellule A1 est renvoyée.

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Function getLastCellWithData(sh As Worksheet) As Range
  Dim r As Long: r = 1
  Dim c As Long: c = 1
  Dim EnableEvents As Boolean
  Dim cell As Range
 
 On Error GoTo EndHandler
  EnableEvents = Application.EnableEvents
  Application.EnableEvents = False
  If sh.Cells.SpecialCells(xlCellTypeLastCell).Address <> "$A$1" Then
    Set cell = sh.Cells.Find(what:="*", after:=sh.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, _
      searchorder:=xlByRows, SearchDirection:=xlPrevious)
    If Not cell Is Nothing Then
      r = cell.Row
      c = sh.Cells.Find(what:="*", after:=sh.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, _
        searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If
  End If
  Set getLastCellWithData = sh.Cells(r, c)
 
EndHandler:
  Application.EnableEvents = EnableEvents
End Function

C'est vrai, il y a bien Cells.SpecialCells(xlCellTypeLastCell) qui est normalement prévu pour renvoyer la dernière cellule utilisée, mais cette technique souffre d'un problème qui peut être bloquant: SpecialCells(xlCellTypeLastCell) renvoie la dernière cellule utilisée depuis la dernière sauvergarde^*. Certains prétendront alors qu'il suffit d'enregistrer le classeur avant d'utiliser ce code, mais on comprend vite qu'il n'est pas souhaitable d'enregistrer le classeur à la place de l'utilisateur et sans lui demander son avis juste pour cela.

Remarquez que SpecialCells souffre d'un autre handicap, c'est que, pour une raison que j'ignore, il lève l'évènement SelectionChange de la feuille concernée, ce qui peut être assez problématique. il est donc important de désactiver les écoutes d'évènements de l'application et donc, de passer par une gestion d'erreur pour la remettre comme elle était même en cas de levée d'erreur...

^* Si, sur une nouvelle feuille, vous saisissez une valeur par exemple en Z35, Cells.SpecialCells(xlCellTypeLastCell) renverra Z35 (en fait $Z$35). Videz la cellule et réutilisez le code => vous recevrez encore Z35. Même si vous supprimez des lignes et des colonnes englobant Z35, Cells.SpecialCells(xlCellTypeLastCell) continuera à vous renvoyer Z35. Têtu, Excel? Non, parfois bizarrement pensé par ces concepteurs, peut-être...