I have four columns AA, AB, AC, and AD with values that I want to sort after a certain pattern:

Starting Situation

1. All purely numerical values must be moved to the front before all other alphanumerical values.

2. Empty fields must be put to the end.

3. The numerical value with the largest number of characters must be placed in the first column. For example, the value “55555” in Row 7 and Column AD must be after execution of the macro in Row 7 and Column AA.

The final result of the example above should look like this:

Result

My solution is below. The problem is that I couldn’t realize point 2, moving the empty field to the end. Also, I’m using loops, and I want to transform 900000 (!) rows of data. Running the macro like this takes days…any other solution is appreciated. Thank you.

Option Explicit

Sub resort()

    Dim i As Long
    Dim j As Long
    Dim temp As Range
    
    With Worksheets("Tabelle1")
    
    For j = 1 To 10
    
        For i = 2 To 15
        
            If IsNumeric(.Range("AA" & i)) = False And IsNumeric(.Range("AB" & i)) = True Then
            
                .Range("AB" & i).Copy Destination:=.Range("AE" & i)
                .Range("AA" & i).Copy Destination:=.Range("AB" & i)
                .Range("AE" & i).Copy Destination:=.Range("AA" & i)
                .Range("AE" & i).Clear
                            
            End If
        
        Next i
        
        For i = 2 To 15
        
            If IsNumeric(.Range("AB" & i)) = False And IsNumeric(.Range("AC" & i)) = True Then
            
                .Range("AC" & i).Copy Destination:=.Range("AE" & i)
                .Range("AB" & i).Copy Destination:=.Range("AC" & i)
                .Range("AE" & i).Copy Destination:=.Range("AB" & i)
                .Range("AE" & i).Clear
                            
            End If
        
        Next i
        
        For i = 2 To 15
        
            If IsNumeric(.Range("AC" & i)) = False And IsNumeric(.Range("AD" & i)) = True Then
            
                .Range("AD" & i).Copy Destination:=.Range("AE" & i)
                .Range("AC" & i).Copy Destination:=.Range("AD" & i)
                .Range("AE" & i).Copy Destination:=.Range("AC" & i)
                .Range("AE" & i).Clear
                            
            End If
        
        Next i
        
     Next j
        
        
     '++++++++++++++++++++++++++++++++++++++++++++++++++++'
        
     For j = 1 To 10
        
        For i = 2 To 15
        
            If IsNumeric(.Range("AB" & i)) = True Then
            
                If Len(.Range("AB" & i)) > Len(.Range("AA" & i)) Then
            
                    .Range("AB" & i).Copy Destination:=.Range("AE" & i)
                    .Range("AA" & i).Copy Destination:=.Range("AB" & i)
                    .Range("AE" & i).Copy Destination:=.Range("AA" & i)
                    .Range("AE" & i).Clear
                    
                End If
                            
            End If
        
        Next i
    
        For i = 2 To 15
        
            If IsNumeric(.Range("AC" & i)) = True Then
            
                If Len(.Range("AC" & i)) > Len(.Range("AB" & i)) Then
                
                    .Range("AC" & i).Copy Destination:=.Range("AE" & i)
                    .Range("AB" & i).Copy Destination:=.Range("AC" & i)
                    .Range("AE" & i).Copy Destination:=.Range("AB" & i)
                    .Range("AE" & i).Clear
                                
                End If
                
            End If
        
        Next i
      
        For i = 2 To 15

            If IsNumeric(.Range("AD" & i)) = True Then

                If Len(.Range("AD" & i)) > Len(.Range("AC" & i)) Then

                    .Range("AD" & i).Copy Destination:=.Range("AE" & i)
                    .Range("AC" & i).Copy Destination:=.Range("AD" & i)
                    .Range("AE" & i).Copy Destination:=.Range("AC" & i)
                    .Range("AE" & i).Clear

                End If

            End If

        Next i
    
    Next j
        
    End With

End Sub