StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel macro to sort cells in rows by length
text
Body
copied!<p>A couple years ago, by browsing different forums I made myself a macro that was sorting columns by length, longest to shortest (by number of characters in cells). I was pasting special with transpose to a new sheet to get rows listed as columns. I then pasted the VBS code in the macro 100 times so it could do 100 columns per run.<br></p> <p>Today I tried running this macro but it doesn't work at all now :(</p> <p>This is the VBS code I used (without the 100 pastes):</p> <pre><code>Sub SortByLength2() Dim lLoop As Long Dim lLoop2 As Long Dim str1 As String Dim str2 As String Dim MyArray Dim lLastRow As Long lLastRow = Range("A65536").End(xlUp).Row MyArray = Range(Cells(2, 1), Cells(lLastRow, 1)) '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("JO1:JO" & UBound(MyArray) + 1) = (MyArray) Range("A:A").Delete Shift:=xlToLeft End Sub </code></pre> <p>There should be a better solution to sort in rows, without transposing rows to columns and without pasting the same VBS code 100 times...</p> <p>Could anyone help me with the macro that could simply sort cells in rows by length of characters in each cell with unlimited rows and columns? Longest cells should be 1st, shortest - last</p> <p>In my case, I have 745 rows and column range from A to BA.</p> <p>Thanks in advance</p> <p>Update, as per request, a screnshot: <img src="https://i.stack.imgur.com/lNZSi.png" alt="enter image description here"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload