Sort by Length on Range A52:A54

[meabrams]

I need to either adjust this macro so that it will work with the range A52:A54 sorting them by length putting the longest text at the top.

The one I found after I change it around a bit works if starting at A1... if starting any where else it changes everything above to an #N/A

Thank you for your help

Macro I found and currently trying to adjust

Sub SortByLength()
    Dim lLoop As Long
    Dim lLoop2 As Long
    Dim str1 As String
    Dim str2 As String
    Dim MyArray
    Dim lLastRow As Long
     

    MyArray = Range("A52:A54") 'works if set to Range("A1:A3") 
     'Sort array
    For lLoop = 1 To UBound(MyArray)
        For lLoop2 = lLoop To UBound(MyArray)
            If Len(MyArray(lLoop2, 1)) > Len(MyArray(lLoop, 1)) Then
                str1 = MyArray(lLoop, 1)
                str2 = MyArray(lLoop2, 1)
                MyArray(lLoop, 1) = str2
                MyArray(lLoop2, 1) = str1
            End If
        Next lLoop2
    Next lLoop
     'Output sorted array
    Range("A52:A" & UBound(MyArray)) = (MyArray)
End Sub

original macro post here http://www.ozgrid.com/forum/showthread.php?t=80743

[LJMetzger]

Hi meabrams,

Try the following (changes in red):

Sub SortByLength()
    Dim lLoop As Long
    Dim lLoop2 As Long
    Dim str1 As String
    Dim str2 As String
    Dim MyArray As Variant
    Dim lLastRow As Long
     

    MyArray = Range("A52:A54") 'works if set to Range("A1:A3")
     'Sort array
    For lLoop = 1 To UBound(MyArray)
        For lLoop2 = lLoop To UBound(MyArray)
            If Len(MyArray(lLoop2, 1)) > Len(MyArray(lLoop, 1)) Then
                str1 = MyArray(lLoop, 1)
                str2 = MyArray(lLoop2, 1)
                MyArray(lLoop, 1) = str2
                MyArray(lLoop2, 1) = str1
            End If
        Next lLoop2
    Next lLoop
     'Output sorted array
    Range("A52:A" & (51 + UBound(MyArray))) = (MyArray)
End Sub

Lewis

[jindon]

meabrams,

Isn't it just

Sub test()
    Dim a, i As Long, ii As Long, temp
    With [a52:a54]
        a = .Value
        For i = 1 To UBound(a, 1) - 1
            For ii = i + 1 To UBound(a, 1)
                If Len(a(i, 1)) < Len(a(ii, 1)) Then
                    temp = a(i, 1)
                    a(i, 1) = a(ii, 1)
                    a(ii, 1) = temp
                End If
            Next
        Next
        .Value = a
    End With
End Sub

[meabrams]

I will try these both after I get up in the afternoon...

[meabrams]

Thank you both I ended up going with LJMetzger way, but I will keep jindon way for next time it does look cleaner though