Thread: Sorting rows
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
merjet merjet is offline
external usenet poster
 
Posts: 812
Default Sorting rows

Copy | Paste Special | Transpose the numbers to another range of
cells. Sort the result, each column independently. Copy | Paste
Special | Transpose the sorted numbers back to their original
location. Since you have 4000 rows (and unless you have Excel 2007),
you will have to do it in chunks. The following Sub should help with
the sorting. Change the number of columns, etc. to suit.

Sub Macro1()
Dim iEnd As Integer
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
iEnd = ws.Range("A1").End(xlDown).Row
For iCol = 1 To 4
ws.Range(Cells(1, iCol), Cells(iEnd, iCol)).Sort _
Key1:=Cells(2,iCol), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Next iCol
End Sub

Hth,
Merjet