PDA

View Full Version : Solved: Macro to sort data ascending



rhudgins
04-06-2010, 02:47 PM
Each example contains 21 rows starting in row 66. Each example has one ticker that is either Long or Short and 20 tickers that are either a Long Basket or Short Basket.

For each example I would like to sort ascending the 20 tickers that are either a Long Basket or Short Basket.

Any help would be great. Thanks!

mdmackillop
04-06-2010, 03:34 PM
So you want to sort B66:S86 (equivalent) by column B ascending in each group?

rhudgins
04-06-2010, 03:39 PM
No. For each group I would like to sort F67:F86 ascending and that is all

mdmackillop
04-06-2010, 04:14 PM
Part working, no time to figure out the bug!

Sub Dosort()
Dim c As Range, Rng As Range
Set c = Range("A66")
Set Rng = c.Offset(1, 5).Resize(20)
ActiveSheet.Sort.SortFields.Clear
Do
Set Rng = c.Offset(1, 5).Resize(20)
Rng.Select
With ActiveSheet.Sort
.SortFields.Add Key:=Rng(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Rng
.Header = xlNo
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set c = c.End(xlDown)
Loop Until c.Row = Rows.Count
End Sub

rhudgins
04-06-2010, 04:26 PM
Thanks but how am I supposed to figure out how to fix the bug? I have no idea!?

mdmackillop
04-07-2010, 01:50 AM
Option Explicit
Sub Dosort()
Dim c As Range, Rng As Range
Set c = Range("A66")
Set Rng = c.Offset(1, 5).Resize(20)
Do
Set Rng = c.Offset(1, 5).Resize(20)
Rng.Sort Key1:=Rng(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Set c = c.End(xlDown)
Loop Until c.Row = Rows.Count
End Sub

rhudgins
04-07-2010, 05:30 AM
Thank you