Voir le flux RSS

Pierre Fauconnier

VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille

Note : 2 votes pour une moyenne de 5,00.
par , 15/10/2019 à 08h00 (647 Affichages)
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...

Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog Viadeo Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog Twitter Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog Google Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog Facebook Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog Digg Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog Delicious Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog MySpace Envoyer le billet « VBA Excel: Snippet pour trouver la dernière cellule utilisée d'une feuille » dans le blog Yahoo

Mis à jour 20/05/2020 à 21h40 par Pierre Fauconnier

Catégories
VBA , Excel , MS Office , Snippet

Commentaires

  1. Avatar de MarcelG
    • |
    • permalink
    Salut Pierre,

    Pour ma part, je quitte peu à peu le processus dernl = ....End(xlUp)

    (il était temps, au bout de 11 ans )

    Je me méfie de SpecialCells qui m'a donné parfois des résultats surprenants (je n'ai pas d'exemple précis sous les yeux)

    Je lui préfère la méthode Find

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    With Worksheets(1)
                With .Range("A1", .Cells(.Rows.Count, .Columns.Count))
                        dernl = .Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows, LookIn:=xlValues).Row
                        derncol = .Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByColumns, LookIn:=xlValues).Column
                End With
    End With

    Au niveau de la ligne, pour un tableau structuré (là aussi, j'ai tardé!)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    derligne = Worksheets("Entrée").ListObjects("Tableau1").ListColumns("Date").Range.Find("", SearchDirection:=xlNext).Row-1
    On notera au passage l'importance du paramètre SearchDirection dans les 2 cas de figure

    Je sais au demeurant que ces notions font débat.

    C'était donc juste mon - humble - avis

    Bonne journée à toi et à la prochaine.
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut Marcel,

    Le problème de ta procédure, c'est que si ta feuille est vide, ça plante. Le SpecialCells que je j'utilise ne sert qu'à éviter le plantage dans ce cas de figure.


    Pour ce qui est de la dernière ligne d'un tableau, il faut voir si l'on parle de la ligne absolue (la ligne de la feuille) ou la ligne du tableau. Pour avoir la dernière ligne du tableau, il suffit de compter le nombre de lignes du tableau:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    Function LastTableRow(Table As ListObject) As Long
      LastTableRow = Table.ListRows.Count
    End Function
    Si on parle de la ligne absolue (celle de la feuille), on peut utiliser
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    Function LastAbsoluteTableRow(Table As ListObject) As Long
      If Table.ListRows.Count > 0 Then LastAbsoluteTableRow = Table.DataBodyRange(Table.ListRows.Count, 1).Row
    End Function
  3. Avatar de MarcelG
    • |
    • permalink
    Salut Pierre,

    D'accord.
    Merci pour ces précisions.

    A plus tard.