How should I sort my data?

**jjbigfish** · 09-27-2012, 02:00 PM

I have an attached example file. I am looking to sort column B by size. The issue that I am having is that when I select to sort by column B Z-A for largest to smallest, it doesn't put it in that order, the first SKU 15505 comes in correct, with the largest being first, but the second SKU 19208 doesn't sort by largest size, the largest row comes in at row 30. How should I go about fixing this?

**stanleydgromjr** · 09-27-2012, 02:35 PM

jjbigfish,

What determines the size of the data in range B2:B38?

Is it the length of the string?

**jjbigfish** · 09-27-2012, 02:42 PM

SOLVED, I used a add-in that can sort by text length.

**stanleydgromjr** · 09-27-2012, 02:47 PM

jjbigfish,

If you only have data in columns A and B, then:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Option Explicit
Sub SortByCol_B_Descending()
' stanleydgromjr, 09/27/2012
' http://www.excelforum.com/excel-general/863928-how-should-i-sort-my-data.html?p=2948204
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Range("C2:C" & lr)
  .FormulaR1C1 = "=LEN(RC[-1])"
  .Value = .Value
End With
Range("A2:C" & lr).Sort key1:=Range("C2"), order1:=2
Range("C2:C" & lr).ClearContents
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SortByCol_B_Descending macro.