Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

how do you sort multiple colums at once?

Posted by sean on March 06, 2001 9:10 AM
hi,

I have many coluns of numbers. I want to sort each data set in each column from smallest number to largest. How do I sort all the columns at once instead of highlighting each column one at a time and sorting?


Check out our Excel VBA Resources

Re: how do you sort multiple colums at once?

Posted by Mark W. on March 06, 2001 9:20 AM
Excel's Data Sort... menu command allows you to
sort as many as 3 columns in one pass. If you
need to sort more columns than this you can
use the macro recorder to create a procedure that
can be reused.

Re: how do you sort multiple colums at once?

Posted by Dave Hawley on March 06, 2001 9:20 AM


Hi Sean

You may need a macro for this, so here is one.
To use it Push Alt+F11 and go to Insert>Module.
Paste in this code:

Sub MultiSort()
Dim Cols As Range, i As Integer
Set Cols = Selection
For i = 1 To Cols.Columns.Count
Cols.Columns(i).Sort Key1:=Cols.Columns(i).Cells(1, 1), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Next i
End Sub

Push Alt+Q and save.

Select all your columns you want to sort (including headings) and push Alt+F8.

Click "MultiSort" then "Run"


Dave

OzGrid Business Applications


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.