XL 2010 Utilisation de UsedRange

Sub test()
    Dim plage As Range
    'set plage=RealUsedrange(object worksheet)'méthode
    Set plage = RealUsedrange
    If plage Is Nothing Then
     
       MsgBox "cette feuille n'est pas utilisée"
    Else
        MsgBox plage.Address
    End If
End Sub

Function RealUsedrange(Optional feuille As Worksheet = Nothing) As Range
    Dim L1&, L2, C1, C2, Cel As Range
    'ordre des arguments du find ;What ,After,LookIn,LookAt,SearchOrder,SearchDirection ,MatchCase,MatchByte,SearchFormat

    If feuille Is Nothing Then Set feuille = ActiveSheet

    With feuille
        'debut du usedrange
        'recherche par ligne a partir du debut
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlNext, False)
        If Not Cel Is Nothing Then L1 = Cel.Row Else L1 = 1

        'recherche par colonne a partir du debut
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlNext, False)
        If Not Cel Is Nothing Then C1 = Cel.Column Else C1 = 1
        '----------------------------------------------------------------------
        'fin du usedrange
        'recherche par ligne depuis la fin
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious, False)
        If Not Cel Is Nothing Then L2 = Cel.Row Else L2 = 1

        'recherche par colonne depuis la fin
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious, False)
        If Not Cel Is Nothing Then C2 = Cel.Column Else C2 = 1

        If L1 + L2 + C1 + C2 = 4 Then
            Set RealUsedrange = Nothing
        Else
            Set RealUsedrange = .Range(.Cells(L1, C1), .Cells(L2, C2))
        End If

    End With
End Function

Sub test()
    Dim plage As Range
    'set plage=RealUsedrange(object worksheet)'méthode
    Set plage = RealUsedrange
    If plage Is Nothing Then
      
       MsgBox "cette feuille n'est pas utilisée"
    Else
        MsgBox plage.Address
    End If
End Sub

Function RealUsedrange(Optional feuille As Worksheet = Nothing) As Range
    Dim L1&, L2, C1, C2, Cel As Range
    'ordre des arguments du find ;What ,After,LookIn,LookAt,SearchOrder,SearchDirection ,MatchCase,MatchByte,SearchFormat

    If feuille Is Nothing Then Set feuille = ActiveSheet

    With feuille
        'debut du usedrange
        'recherche par ligne a partir du debut
        Set Cel = .Cells.Find("*", , , , xlByRows, xlNext, False)
        If Not Cel Is Nothing Then L1 = Cel.Row Else L1 = 1

        'recherche par colonne a partir du debut
        Set Cel = .Cells.Find("*", , , , xlByColumns, xlNext, False)
        If Not Cel Is Nothing Then C1 = Cel.Column Else C1 = 1
        '----------------------------------------------------------------------
        'fin du usedrange
        'recherche par ligne depuis la fin
        Set Cel = .Cells.Find("*", , , , xlByRows, xlPrevious, False)
        If Not Cel Is Nothing Then L2 = Cel.Row Else L2 = 1

        'recherche par colonne depuis la fin
        Set Cel = .Cells.Find("*", , , , xlByColumns, xlPrevious, False)
        If Not Cel Is Nothing Then C2 = Cel.Column Else C2 = 1

        If L1 + L2 + C1 + C2 = 4 Then
            Set RealUsedrange = Nothing
        Else
            Set RealUsedrange = .Range(.Cells(L1, C1), .Cells(L2, C2))
        End If

    End With
End Function

Sub NettoieEtDerniereCellule()     ' Laurent Longre 2000
Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String
On Error Resume Next
Calc = Application.Calculation
With Application
  .Calculation = xlCalculationManual
  .StatusBar = "Nettoyage en cours..."
  .EnableCancelKey = xlErrorHandler
  .ScreenUpdating = False
End With
For Each Sht In Worksheets
  If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
    Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
    If Not DCell Is Nothing Then
      Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Clear
      Set DCell = Nothing
      Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
      If Not DCell Is Nothing Then _
         Sht.Range(DCell, Sht.[IV1]).EntireColumn.Clear
    End If
    Rien = Sht.UsedRange.Address
  End If
Next Sht
Application.StatusBar = False
Application.Calculation = Calc
End Sub