samwang

1. Sub tt()
   
2. Dim Arr, Brr, n&
   
3. n = [A65536].End(xlUp).Row
   
4. With [B2].Resize(n - 1)
   
5.     .Formula = "=LEN(A2)"
   
6.     .Value = .Value
   
7. End With
   
8. 
   
9. With Range([A1], [B65536].End(xlUp))
   
10.     Brr = .Value
    
11.     .Sort Key1:=.Item(2), Order1:=xlAscending, Header:=xlYes
    
12.     Arr = .Value
    
13.     .Value = Brr
    
14. End With
    
15. 
    
16. [d1].Resize(UBound(Arr)) = Arr
    
17. Range("B2:B" & n) = ""
    
18. End Sub

½Æ»s¥N½X

samwang

¦^´_ 14# s13030029

­É¥Î­ã¤j­×§ï¦p¤U
Sub TEST()
Dim R&, Arr, LN&
R = Cells(Rows.Count, 1).End(xlUp).Row
If R < 3 Then Exit Sub
With Range("A2:A" & R)
    Arr = .Value
    For i = 1 To .Count
        ipos = InStr(5, Arr(i, 1), "-")
        If ipos > o Then
            xR = Mid(Arr(i, 1), 1, ipos - 1)
            Arr(i, 1) = 100 + IIf(LN = 0, 99, ipos - 1) & "|" & Arr(i, 1)
        Else
            LN = Len(Arr(i, 1))
            Arr(i, 1) = 100 + IIf(LN = 0, 99, LN) & "|" & Arr(i, 1)
        End If
      Next i
      .Value = Arr
      .Sort Key1:=.Item(1), Order1:=xlAscending, Header:=xlNo
      .Replace "*|", "", Lookat:=xlPart
End With
End Sub