How to Do a Full 3‐Column Macro Sort in Excel

Step-by-Step Guide

1. 1

   Step 1: Open a new XL worksheet from the XL icon on the dock or from within the Applications folder.

   Use the type xlsm because it will contain macros.
   
   Save it to a logical file folder such as 'LifeGuide Hub Macros' or 'LifeGuide Hub XL Articles'
   
   etc.,,,,,,,,,,,,, Keep the header Sub (routine) line and other initial comments, etc. to be edited:
   Sub SortFullThreeColumnsData() ' Keyboard Shortcut:
   Option+Cmd+s 'The data to be sorted must be fully selected first, without headers ' Header for temp sort area.
   
   Range("IV1").Value = "Header" ' Move data to temp sort location.
   
   For Each cell In Selection Range("iv65536").End(xlUp).Offset(1, 0) = cell.Value Next cell ' Sort numbers ascending Range("IV1"
   
   Range("IV1").End(xlDown)).Sort Key1:=Range("IV2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' Move sorted data back to original location.
   
   Selection(1, 1).Activate CCnt = Selection.Columns.Count RCnt = Selection.Rows.Count CellCnt = Selection.Cells.Count Bcell = 2 For c = 1 To CCnt For r = 1 To RCnt Range(ActiveCell.Address).Offset(r
   - 1, c
   - 1).Value = _ Range("iv" & Bcell).Value Bcell = Bcell + 1 Next r Next c ' Clear temp sort area Range("IV1"
   
   Range("IV1").End(xlDown)).Clear End Sub ,, Verify that the SortFullThreeColumnsData() macro performed a Full Sort of the three columns of data. ,
2. 2

   Step 2: Save it under filename Sorting Full Columns worker.

3. 3

   Step 3: Select cell region A9:C14 and enter the following data to the indicated cells: A9: 5 ... B9:1 ... C9: 11 A10: 2 ... B10: 14 ... C10: 2 A11: 2 ... B11: 13 ... C11: 3 A12: 4 ... B12: 9 ... C12: 14 A13: 11 ... B13: 4 ... C13: 20 A14: 2 ... B14: 13 ... C14: 2

4. 4

   Step 4: Enter the label Original Data to cell D9.

5. 5

   Step 5: Enter the label Disconnected to cell D10.

6. 6

   Step 6: Enter the label Sorted Data to cell D1.

7. 7

   Step 7: Enter the label Disconnected to cell D2.

8. 8

   Step 8: Choose menu item or tool Macro(s) > Record New Macro.

9. 9

   Step 9: Name the macro SortFullThreeColumnsData and give it the macro shortcut key

10. 10

    Step 10: and the description

11. 11

    Step 11: Full sort of three columns by 6 rows of data.

12. 12

    Step 12: Hit OK.

13. 13

    Step 13: Do a few bogus actions on the worksheet to record a dummy macro.

14. 14

    Step 14: Select Tools Macro > Stop Macro.

15. 15

    Step 15: Select Tools Macro > Visual Basic Editor.

16. 16

    Step 16: Click on Module1 in the window that pops up.

17. 17

    Step 17: Overwrite the macro code that appears there with the following code.

18. 18

    Step 18: Copy the data in cell range A9:C14 and paste it to cell range A1:C6.

19. 19

    Step 19: Run menu item Tools Macros Macros and run the SortFullThreeColumnsData macro or press ⌥ Option+⌘ Cmd+S (on a Mac) or Alt+⌘ Command+S (on a PC).

20. 20

    Step 20: Save the worksheet.

Detailed Guide

About the Author