Attribute VB_Name = "DerniereCellule"

Sub test()
    MsgBox DerCell.Address
'    MsgBox DERCELLCURRENT(ActiveCell).Address
End Sub

Sub GetRealLastCell()
'mpfe (doc J@C)
Dim RealLastRow As Long
Dim RealLastColumn As Long
    Range("A1").Select
    On Error Resume Next
    RealLastRow = _
        Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    RealLastColumn = _
        Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
    Cells(RealLastRow, RealLastColumn).Select
End Sub

Function DerCell() As Range
Dim derLi, derCol
    On Error GoTo fin
    derLi = Cells.Find("*", [A1], , , 1, 2).Row
    derCol = Cells.Find("*", [A1], , , 2, 2).Column
    Set DerCell = Cells(derLi, derCol)
    Exit Function
fin:
    Set DerCell = Cells(1, 1)
End Function

Function DERCELLCURRENT(cell As Range) As Range
Dim derLi, derCol
    derLi = cell.CurrentRegion.Row + _
        cell.CurrentRegion.Rows.Count - 1
    derCol = cell.CurrentRegion.Column + _
        cell.CurrentRegion.Columns.Count - 1
    Set DERCELLCURRENT = Cells(derLi, derCol)
End Function

'- pour trouver la dernière cellule remplie (que ce soit une constante ou
'formule) :
'LL, mpfe
Function DerLi1()
    DerLi1 = Cells.Find("*", [A1], SearchDirection:=xlPrevious).Row
End Function

'- pour trouver la dernière cellule contenant une valeur constante, et
'non pas une formule :

Function DerLi2()
    With Cells.SpecialCells(xlCellTypeConstants).Areas
        DerLi2 = .Item(.Count)(.Item(.Count).Count).Row
    End With
End Function