Sorting excel table based on 1 column

You are going to have to bring the table values into an array, perform a custom in-memory sort by splitting the alphabetic characters from the numeric characters in column A (the former descending, the latter ascending).

Sub sortVals()
    Dim srt As Variant, tmp As Variant
    Dim x As Long, a As Long, s As Long

    With Worksheets("Sheet2")
        With .Cells(1, 1).CurrentRegion
            'grab values from primary key column
            With .Resize(.Rows.Count - 1, 10).Offset(1, 0)
                srt = .Cells.Value2
            End With

            'perform custom sort on array
            For s = LBound(srt, 1) + 1 To UBound(srt, 1)
                For a = LBound(srt, 1) To UBound(srt, 1)
                    If Asc(srt(s, 1)) > Asc(srt(a, 1)) Or _
                       Int(Mid(srt(s, 1), 2)) < Int(Mid(srt(a, 1), 2)) Then
                        ReDim tmp(1 To 1, LBound(srt, 2) To UBound(srt, 2))
                        For x = LBound(tmp, 2) To UBound(tmp, 2)
                            tmp(1, x) = srt(a, x)
                            srt(a, x) = srt(s, x)
                            srt(s, x) = tmp(1, x)
                        Next x
                    End If
                Next a
            Next s

            'dump the values back into the worksheet
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                .Cells(1).Resize(UBound(srt, 1), UBound(srt, 2)) = srt
            End With

        End With
    End With
End Sub

      
                    ^{Sample data before custom sorting routine}

      
                    ^{Sample data after custom sorting routine}

Try this:

Sub SortData()

Range("A:X").Select 'Your Data range
ActiveWorkbook.Worksheets("Your sheet name").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Your sheet name").Sort.SortFields.Add Key:=Range("Sorting range IE: X:X"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Your sheet name").Sort
    .SetRange Range("A:X") 'Your data range
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

You need to substitute the ranges with your own.