Sorted, Nice one.

by Ross Mclean, 17/15/05

Sorting data, it’s not that hard with excel. But until recently when a colleague needed to sort some rows, I’d never really given it much thought. I’d only ever really sorted stuff in columns. When I did need to sort by rows, I was more likely to use Edit > Paste Special > Transpose, Data > Sort, Edit > Paste Special > Transpose, not so hot!!
A much better way (the right way!) is to simply change the orientations in the Sort dialog (Data> Sort > Options)
 

The options button lets you switch the orientation of your sort, as well as changing a few other things - don't miss it like me!

Sorting Every Row or Column in a range?
That’s fine, but what if I need to sort every row/column in a range. That’s to say what if I want each row/column to be ordered in descending or ascending order and not to sort my range based on ONE row/column. As far as I know there’s no inbuilt way to do this with Excel, it’s time to call on a little VBA!

This gets the ball rolling

Sub MIE_Sorted()

Dim SWhichWay As String

SWhichWay = InputBox("Yo buddy, which way do ya want to sort this selection?" & _
vbNewLine & vbNewLine & _
"Type 'RA' for rows in Ascending order," _
& vbNewLine & "Type 'RD' for rows in Descending order," _
& vbNewLine & "Type 'CA' for columns in Ascending order," _
& vbNewLine & "Type 'CD' for columns in Descending order" _
& vbNewLine & "Cheers Kid.", "M.I.E Sorted")

Select Case UCase(SWhichWay)
Case "RD"
SortRows (2)
Case "RA"
SortRows (1)
Case "CA"
SortColumns (1)
Case "CD"
SortColumns (2)
End Select

End Sub

These 2 function actually do the work!

Function SortRows(ByVal iWhichWay As Integer)
Dim rRow As Range
For Each rRow In Selection.Rows
rRow.Sort Key1:=Range(rRow.Cells(1).Address), Order1:=iWhichWay, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Next
End Function


Function SortColumns(ByVal iWhichWay As Integer)
Dim rCol As Range
For Each rCol In Selection.Columns
rCol.Sort Key1:=Range(rCol.Cells(1).Address), Order1:=iWhichWay, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next
End Function

The code here uses Excel’s (not VBA’s) built in sort method. I’m lead to believe that this can be slower that some roll your own types, but let’s keep it simple for now! For anyone who wants to know more about sorting the links below give a pretty good start.
 

Links

From Mr Pearson's site
Sorting by Cell Colour in Excel

From dicks blog, a methods that's often overlooked,
Sort On More Than 3 Columns

The news groups, they never let you down!
Sort an Array or List with VBA code (Excel Examples)
Fast sorting routines? (some good VB stuff)
Binary Tree Sorting (good discussion, if not a little feisty)