XL 2013 Trouver nom plage de la cellule active et trier la plage de cellule

Sub Test()
    Dim NomPlage As String
   
    NomPlage = NomPlageCelluleActive
    If Len(NomPlage) > 0 Then Call TrierPlage(NomPlage)
End Sub

'----------------------------------------------------------
'Retourne le 1er nom du Gestionnaire de noms qui représente
'une plage dans laquelle se trouve la cellule active
'----------------------------------------------------------
Function NomPlageCelluleActive() As String
    Dim Nom As Name
    Dim Rng As Range
   
    On Error Resume Next
   
    'Liste des noms
    For Each Nom In ThisWorkbook.Names
        Set Rng = Range(Nom)
       
        If Err.Number Then
            Err.Clear
        Else
            If ActiveCell.Parent.Name = Rng.Parent.Name Then
                If Not Intersect(ActiveCell, Rng) Is Nothing Then Exit For
            End If
        End If
    Next Nom
   
    On Error GoTo 0
    If Not Nom Is Nothing Then NomPlageCelluleActive = Nom.Name
End Function
   
'--------------------------------------------
'Trie la plage nommée sur la colonne indiquée
'--------------------------------------------
Sub TrierPlage(NomPlage As String, Optional NoColonne As Integer = 1)
    Dim Rng As Range
   
    Set Rng = Range(NomPlage)
   
    With Rng.Parent.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Rng.Columns(NoColonne), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub test()
Dim Plage   As Range
Dim Nm      As Name
    ' On vérifie si la cellule fait partie d'un des noms de la feuille
    For Each Nm In Me.Names
        If Not Intersect(Range(Nm.RefersTo), ActiveCell) Is Nothing Then
            Set Plage = Range(Nm.RefersTo)
            Exit For
        End If
    Next
    
    ' Sinon On vérifie si la cellule fait partie d'un des noms du classeur
    If Plage Is Nothing Then
        For Each Nm In ThisWorkbook.Names
            If Not Intersect(Range(Nm.RefersTo), ActiveCell) Is Nothing Then
                Set Plage = Range(Nm.RefersTo)
                Exit For
            End If
        Next
    End If
    
    ' Si on a trouvé la plage, on la trie
    Select Case True
        Case Plage Is Nothing: ' Rien à faire
        Case Plage.Areas.Count > 1: MsgBox "les champs non contigus ne sont pas pris en charge", vbCritical
        Case Plage.Rows.Count > 1 And Plage.Columns.Count > 1:
            Dim T()
            ReDim T(Plage.Cells.Count - 1)
            For I = 0 To Plage.Cells.Count - 1 ' On charge les cellules dans un tableau simple
                T(I) = Plage.Cells(I + 1)
            Next
                For I = 0 To UBound(T) ' On trie ce tableau par ordre croissant
                    x = I
                    For k = x + 1 To UBound(T)
                        If T(k) <= T(x) Then x = k
                    Next k
                    If I <> x Then
                        ValTemp = T(x)
                        T(x) = T(I)
                        T(I) = ValTemp
                    End If
                Next I
            For I = 0 To Plage.Cells.Count - 1 ' On recharge le tableau dans la plage
                Plage.Cells(I + 1) = T(I)
            Next
        Case Else
            ' 1 colonne ou 1 ligne, on utilise les fonctions excel
            With Me.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Plage, _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SetRange Plage
                .Header = xlNo
                .MatchCase = False
                .Orientation = IIf(Plage.Rows.Count = 1, xlLeftToRight, xlTopToBottom)
                .SortMethod = xlPinYin
                .Apply
            End With
    End Select
    

End Sub

Public Sub tri_plage()
Dim nms As Name
    For Each nms In ActiveWorkbook.Names
        If Not Intersect(Range(ActiveCell.Address), Range(Mid(nms.RefersTo, 2))) Is Nothing Then
            With ActiveWorkbook.ActiveSheet.Sort
                With .SortFields
                    .Clear
                    .Add Key:=Range(nms.Name).Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                End With
                .SetRange Range(nms.Name)
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    Next nms
End Sub

Sub testfanch55()
Dim Plage   As Range
Dim Nm      As Name
    ' On vérifie si la cellule fait partie d'un des noms de la feuille
    On Error Resume Next
    For Each Nm In Worksheets(1).Names
        If Not Intersect(ActiveSheet.Range(Nm.RefersTo), ActiveCell) Is Nothing Then
            If Err = 0 Then ' car le nom peut ne pas être un range
                Set Plage = ActiveSheet.Range(Nm.RefersTo)
                Exit For
            Else
                Err.Clear
            End If
        End If
    Next
   
    ' Sinon On vérifie si la cellule fait partie d'un des noms du classeur
    If Plage Is Nothing Then
        On Error Resume Next
        For Each Nm In ThisWorkbook.Names
            If Not Intersect(ActiveSheet.Range(Nm.RefersTo), ActiveCell) Is Nothing Then
                If Err = 0 Then ' car le nom peut ne pas être un range
                    Set Plage = ActiveSheet.Range(Nm.RefersTo)
                    Exit For
                Else
                    Err.Clear
                End If
            End If
        Next
    End If
   
    ' Si on a trouvé la plage, on la trie
    Select Case True
        Case Plage Is Nothing: ' Rien à faire
        Case Plage.Areas.Count > 1: MsgBox "les champs non contigus ne sont pas pris en charge", vbCritical
'        Case Plage.Rows.Count > 1 And Plage.Columns.Count > 1:
        Case Else
            Dim T()
            ReDim T(Plage.Cells.Count - 1)
            For I = 0 To Plage.Cells.Count - 1 ' On charge les cellules dans un tableau simple
                T(I) = Plage.Cells(I + 1)
            Next
                For I = 0 To UBound(T) ' On trie ce tableau par ordre croissant
                    x = I
                    For k = x + 1 To UBound(T)
                        If T(k) <= T(x) Then x = k
                    Next k
                    If I <> x Then
                        ValTemp = T(x)
                        T(x) = T(I)
                        T(I) = ValTemp
                    End If
                Next I
            For I = 0 To Plage.Cells.Count - 1 ' On recharge le tableau dans la plage
                Plage.Cells(I + 1) = T(I)
            Next
    End Select
   

End Sub

Sub tri()
    If ActiveCell.ListObject Is Nothing Then Exit Sub
    With ActiveCell.ListObject
    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=.ListColumns(1).Range, _
        SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
End Sub

    If ActiveCell.ListObject.Name <> "Test25" Then Exit Sub

Sub test()
    MsgBox GetNameOfRangeWithOneCell()
End Sub

Function GetNameOfRangeWithOneCell(Optional cel As Range = Nothing)
       If cel Is Nothing Then Set cel = ActiveCell
    On Error Resume Next
    Nom = cel.ListObject.Name
    If Err > 0 Then
        Nom = "No Name!!"
        For Each n In Names
            If Not Intersect(Range(n), cel) Is Nothing Then Nom = n.Name
        Next
    End If
    GetNameOfRangeWithOneCell = Nom
End Function

    .Sort.SortFields.Clear
    .DataBodyRange.AutoFilter

Sub tri()
    If Not ActiveCell.ListObject Is Nothing Then
        With ActiveCell.ListObject
            ShowFilter = .ShowAutoFilter
            With .Sort
                .SortFields.Clear
                .SortFields.Add2 Key:=.Parent.ListColumns(1).Range, _
                        SortOn:=xlSortOnValues, Order:=xlAscending, _
                        DataOption:=xlSortTextAsNumbers ' ou .add
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .Apply
                .SortFields.Clear ' ou pas
            End With
            If Not ShowFilter And .ShowAutoFilter Then .DataBodyRange.AutoFilter
        End With
    End If
End Sub