Excel 2007 : Sorting adjacent columns

**TraceCadet** · 08-31-2010, 09:04 PM

Hi, I am trying to sort multiple columns, irrespective of adjacent columns. I want every column to sort from smallest to largest number value. I'd like to do the whole worksheet at once without having to sort one column at a time. Is there a way to do this? Thanks

**Paul** · 08-31-2010, 09:46 PM

Hi TraceCadet, welcome to the forum.

You can do this using a macro, like the one provided below. This uses Excel 2007 VBA language, and I'm not sure it will work in 2003 or earlier as the syntax has changed. If it doesn't, try recording a macro of you manually sorting one column from A-Z, then compare the recorded macro to mine.

This macro loops through all used columns in a worksheet (Sheet1 in this case) and sorts them A-Z. Hope it helps!

Please Login or Register  to view this content.

**TraceCadet** · 09-01-2010, 11:54 AM

Wow - thanks for the quick response. It worked perfectly - thanks so much!!

**TraceCadet** · 07-07-2011, 02:29 PM

Greetings, I figured this out last year when I was using Excel 2003 but now I have upgraded to 2007 and I'm not sure where or how to enter the macro. (I looked back at the spreadsheet I had built this into and can't recall how I ran the macro.) I have a whole different spreadsheet but need to do the same thing - sort a bunch of columns independently. Thank you, Tracy

**Paul** · 07-11-2011, 07:55 PM

Hi Tracy,

In your new workbook, press ALT+F11 to open the VB Editor window. On the left side you should see an entry similar to

VBAProject (your_workbook_name.xlsx)

If you expand that you'll see 'Microsoft Excel Objects', and futher expansion reveals the various worksheets within your workbook as well as a 'ThisWorkbook' module. Double-click the 'ThisWorkbook' entry, then copy my code above and paste it into the code window to the right of 'ThisWorkbook'.

You can then close the VB Editor (after reviewing the code to make sure it's going to point to the correct worksheet). As mentioned previously, it's currently set to look at a worksheet named "Sheet1" and to sort every column from A-Z/0-9. (It determines the last used column by looking in the first row for the last column that has a value.)

If/when you try to save the workbook, you'll have to save it as an XLSM file (Excel Macro Enabled) rather than the standard XLSX. Alternatively you could save it in the Office 2003 "XLS" format, but you may lose some functionality depending on whether or not your new workbook uses functions/display characteristics only available since Excel 2007.

Finally, to run the macro: when looking at the worksheet(s), just press ALT+F8 to open the Macros dialog box. Pick this macro from the list (this one is named "test") and click Run.