Need a sorting solution

[Patrick Simonds]

I have a worksheet with 5 columns and 5 rows. A number gets entered into
each cell (A1:E5). I need to sort so that each number is placed in
sequential order (smallest to largest) across the columns and down the rows
(see example below). Can this be done?


1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

 

[Earl Kiosterud]

Posted in other groups.

 

[Myrna Larson]

You can do this with formulas involving the SMALL function. If the range is
large, it's quite slow to update.

Some years back I wrote a VBA macro to sort in-place which ran quite fast. You
can find it on Google. Search for SortInPlace (all one word), author Myrna
Larson.

 

[Guest]

This macro should do it assuming the acivesheet has your data:

Sub SortIt()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim myRange As Range
Dim Cell As Range
Dim tempSheet As Worksheet
Dim counter As Long

Set myRange = Activesheet.Range("A1:E5")
Set tempSheet = Sheets.Add

counter = 0
For Each Cell In myRange
counter = counter + 1
tempSheet.Cells(counter, 1).Value = Cell.Value
Next Cell

tempSheet.Range(Cells(1, 1), Cells(counter, 1)). _
Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess

counter = 0
For Each Cell In myRange
counter = counter + 1
Cell.Value = tempSheet.Cells(counter, 1).Value
Next Cell

tempSheet.Delete

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Regards
Rowan

 

[Dave Peterson]

You've got more suggestions at your other posts in .excel.