VBA Excel 2007 multilevel sort

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:

Code:
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
 
Upvote 0
Try:
Code:
Sub sortmydata()
[a1].CurrentRegion.Sort [i2], 1, [o2], , 2, , , 1
End Sub
Regards
Northwolves
 
Upvote 0
Thanks, got it to work in the end, but your way looks cleaner, so will try out later.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top