Need a sorting solution

  • Thread starter Thread starter Patrick Simonds
  • Start date Start date
P

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
 
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.
 
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
 
Back
Top