VBA Excel 2007 multilevel sort

[JackDanIce]

Hi,

Have tried searching through posts and on google but can't find solution to my sorting problem.

I have worksheets with matching column headers but with varying rows in different workbooks. I want to sort the data in each worksheet and then collate into a summary page. I can do all of this, except the actual sort and the macro recorder is not helping. And then ontop of that, I tried to re-code the macro recording and made it worse:

Sub sortmydata()

Dim myRange As Range
    
Range("A1", Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
    
Set myRange = Range("A1", Selection.End(xlToRight)).Range(Selection, Selection.End(xlDown))
' to account for number of rows being different each time this macro is
' called, but I'm not sure if I've coded this correctly
    
myRange.Select
With .sort
.SortFields.Clear
.SortFields.Add Key:= _
.Range("I:I"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange .myRange
.Header = xlYes
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
    
' I need to sort by columns I ascending and then column O descending
' I'm aware above doesn't have a 2nd sort key, but can't work out how
' to include it
 
' after here I'm fine
 
 
 
End Sub

 

[JackDanIce]

Realised I didn't close the tag in my HTML coding:

Hi,

Have tried searching through posts and on google but can't find solution to my sorting problem.

I have worksheets with matching column headers but with varying rows in different workbooks. I want to sort the data in each worksheet and then collate into a summary page. I can do all of this, except the actual sort and the macro recorder is not helping. And then ontop of that, I tried to re-code the macro recording and made it worse:

Sub sortmydata()

Dim myRange As Range

Range("A1", Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Set myRange = Range("A1", Selection.End(xlToRight)).Range(Selection, Selection.End(xlDown))
' to account for number of rows being different each time this macro is
' called, but I'm not sure if I've coded this correctly

myRange.Select
With .sort
.SortFields.Clear
.SortFields.Add Key:= _
.Range("I:I"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange .myRange
.Header = xlYes
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' I need to sort by columns I ascending and then column O descending
' I'm aware above doesn't have a 2nd sort key, but can't work out how
' to include it

' after here I'm fine

End Sub

 

[northwolves]

Try:

Sub sortmydata()
[a1].CurrentRegion.Sort [i2], 1, [o2], , 2, , , 1
End Sub

Regards
Northwolves

 

[JackDanIce]

Thanks, got it to work in the end, but your way looks cleaner, so will try out later.