Excel 2003 - 5 Ebenen sortieren

=B2 & TEXT(E2;"000000,00") & TEXT(F2;"0000") & K2 & L2

=RECHTS(WIEDERHOLEN(" ";20)&B2;20) & TEXT(E2;"000000,00") & TEXT(F2;"0000") & RECHTS(WIEDERHOLEN(" ";20)&K2;20) & RECHTS(WIEDERHOLEN(" ";20)&L2;20)

Option Explicit

Const MaxCellChar = 64
'Konstante anpassen, wenn Zellen mehr als 64 Zeichen enthalten (32, 64, 128)  

Public WithEvents BtnOK As MSForms.CommandButton
Public WithEvents BtnClear As MSForms.CommandButton
Public WithEvents BtnCancel As MSForms.CommandButton

Public WithEvents CBoxColumns As MSForms.ComboBox
Public WithEvents LabelColumns As MSForms.Label

Public WithEvents OptionOrderAZ As MSForms.OptionButton
Public WithEvents OptionOrderZA As MSForms.OptionButton
Public WithEvents OptionHeaderOn As MSForms.OptionButton
Public WithEvents OptionHeaderOff As MSForms.OptionButton

Const adVarChar = 200
Const adFldIsNullable = 32

Dim FormEvents As New Collection

Sub InitUserForm()
    Dim Char As String, i As Integer
        
    If Selection.Count < 2 Then
        MsgBox "Kein Bereich ausgewählt!", vbExclamation, "Fehler":  Exit Sub  
    End If
    
    With FormSort
       .Caption = "Daten sortieren":  .Height = 212:  .Width = 222  
        
        With .Controls
            .Add "Forms.Frame.1", "FrameColumns"  
            .Add "Forms.Frame.1", "FrameOrder"  
            .Add "Forms.Frame.1", "FrameHeader"  
        End With
        
        With .Controls("FrameColumns")  
            .Caption = "Spalten auswählen"  
            .TabIndex = 0
            .Height = 54: .Left = 12: .Top = 12: .Width = 194
            
             Set FormSort.LabelColumns = .Controls.Add("Forms.Label.1", "LabelColumns")  
             Set FormSort.CBoxColumns = .Controls.Add("Forms.ComboBox.1", "CBoxColumns")  
             Set FormSort.BtnClear = .Controls.Add("Forms.CommandButton.1", "BtnClear")  
        End With
        
        With .LabelColumns
            .Height = 16: .Left = 12: .Top = 25: .Width = 168
            .BackColor = &HFFFFFF
            .SpecialEffect = fmSpecialEffectSunken
        End With
             
        With .CBoxColumns
            .TabIndex = 0
            .Height = 15.75: .Left = 12: .Top = 6: .Width = 72
        End With
            
        With .BtnClear
            .Accelerator = "C"  
            .Caption = "Clear"  
            .TabIndex = 1
            .Height = 18: .Left = 119: .Top = 4: .Width = 60
        End With
        
        With .Controls("FrameOrder")  
            .Caption = "Sortieren"  
            .TabIndex = 1
            .Height = 36: .Left = 12: .Top = 72: .Width = 194
             
             Set FormSort.OptionOrderAZ = .Controls.Add("Forms.OptionButton.1", "OptionOrderAZ")  
             Set FormSort.OptionOrderZA = .Controls.Add("Forms.OptionButton.1", "OptionOrderZA")  
        End With
        
        With .OptionOrderAZ
            .Accelerator = "U"  
            .Caption = "Aufsteigend"  
            .Value = True
            .TabIndex = 0
            .Height = 18: .Left = 10: .Top = 6: .Width = 78
        End With
        
        With .OptionOrderZA
            .Accelerator = "B"  
            .Caption = "Absteigend"  
            .TabIndex = 1
            .Height = 18: .Left = 108: .Top = 6: .Width = 78
        End With
        
        With .Controls("FrameHeader")  
            .Caption = "Daten enthalten"  
            .TabIndex = 2
            .Height = 36: .Left = 12: .Top = 114: .Width = 194
            
             Set FormSort.OptionHeaderOn = .Controls.Add("Forms.OptionButton.1", "OptionHeaderOn")  
             Set FormSort.OptionHeaderOff = .Controls.Add("Forms.OptionButton.1", "OptionHeaderOff")  
        End With
             
        With .OptionHeaderOn
            .Accelerator = "E"  
            .Caption = "eine Überschrift"  
            .TabIndex = 0
            .Height = 18: .Left = 10: .Top = 6: .Width = 78
        End With
        
        With .OptionHeaderOff
            .Accelerator = "K"  
            .Caption = "keine Überschrift"  
            .Value = True
            .TabIndex = 1
            .Height = 18: .Left = 108: .Top = 6: .Width = 78
        End With
        
        Set .BtnOK = .Controls.Add("Forms.CommandButton.1", "BtnOK")  
        Set .BtnCancel = .Controls.Add("Forms.CommandButton.1", "BtnCancel")  
        
        With .BtnOK
            .Accelerator = "O"  
            .Caption = "OK"  
            .TabIndex = 3
            .Height = 18: .Left = 36: .Top = 162: .Width = 60
        End With
        
        With .BtnCancel
            .Accelerator = "A"  
            .Caption = "Abbrechen"  
            .TabIndex = 4
            .Height = 18: .Left = 120: .Top = 162: .Width = 60
        End With
        
        FormEvents.Add .BtnOK
        FormEvents.Add .BtnCancel
        FormEvents.Add .BtnClear
        FormEvents.Add .CBoxColumns
    End With
    
    With CBoxColumns
        For i = 0 To UBound(Selection.Value, 2) - 1
             Char = Chr(Asc("@") + Selection.Column + i)  
            
            .AddItem "Spalte " & Char  
            .List(.ListCount - 1, 1) = Char
        Next
       
       .ListIndex = 0
        LabelColumns = " " & .List(0, 1)  
       .SetFocus
        Show
    End With
End Sub

Private Sub CBoxColumns_Change()
    With CBoxColumns
        If .ListIndex < 0 Then
            Exit Sub
        ElseIf LabelColumns = "" Then  
            LabelColumns = " " & .List(.ListIndex, 1)  
        ElseIf InStr(LabelColumns, .List(.ListIndex, 1)) = 0 Then
            LabelColumns = LabelColumns & ";" & .List(.ListIndex, 1)  
        End If
    End With
End Sub

Private Sub BtnClear_Click()
    LabelColumns = ""  
End Sub

Private Sub BtnCancel_Click()
    Unload Me
End Sub

Private Sub BtnOK_Click()
    If LabelColumns <> "" Then Call Sortieren  
    Unload Me
End Sub

Private Sub Sortieren()
    Dim RS As Object, Cols As Variant, SortText As String, SortOrder As String, i As Long, r As Long
    Dim ColCount As Long, FirstCol As Long, LastCol As Long, FirstRow As Long, LastRow As Long
    
    Set RS = CreateObject("ADOR.Recordset")  

    With Selection
        If OptionHeaderOn = True Then FirstRow = .Row + 1 Else FirstRow = .Row
        
        If UBound(.Value, 1) = Rows.Count Then
            LastRow = Cells(Rows.Count, CBoxColumns.List(0, 1)).Rows.End(xlUp).Row
        Else
            LastRow = .Row + UBound(.Value, 1) - 1
        End If
        
        ColCount = UBound(.Value, 2)
        
        FirstCol = .Column:  LastCol = .Column + ColCount - 1
    End With
    
    If OptionOrderAZ = True Then SortOrder = " ASC," Else SortOrder = " DESC,"  
            
    Cols = Split(Trim(LabelColumns), ";")  
    
    For i = 0 To UBound(Cols)
        SortText = SortText & Cols(i) & SortOrder
    Next
    
    SortText = Mid(SortText, 1, Len(SortText) - 1)
    
    With RS
        For i = 0 To ColCount - 1
            .Fields.Append CBoxColumns.List(i, 1), adVarChar, MaxCellChar, adFldIsNullable
        Next
    
       .Open
    
        For r = FirstRow To LastRow
           .AddNew
            For i = 0 To ColCount - 1
               .Fields(i) = Cells(r, FirstCol + i)
            Next
           .Update
        Next
    
       .Sort = SortText:  .Update
       
        Application.ScreenUpdating = False
         
        Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).ClearContents
        
        For r = FirstRow To LastRow
            For i = 0 To ColCount - 1
                Cells(r, FirstCol + i) = .Fields(i)
            Next
           .MoveNext
        Next
       
       .Close
        
        Application.ScreenUpdating = True
    End With
End Sub

Option Explicit

Sub Sortieren()
    FormSort.InitUserForm
End Sub