sorting named ranges 1

[sedgely]

i have an excel sheet with 4 named ranges, "pa", "pb", "pc", "pd". I need to sort these by the 11th then 6th column in each range.
Can someone point me in the right direction?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[ClulessChris]

As a starting point record a macro and perform the action manualy. then check the VBA editor to see the code produced.

Good luck.

Everybody is somebodys Nutter.

 

[DaleWatson123321]

Craig,

It's always good to experiment / explore by recording macros; however, you'll likely find the following useful.

Please Note: If the ranges include field names, then you'll need to change the rows that reference the Header - i.e. change "Header:=xlNo" to "Header:=xlYes".

From your description, your ranges are on one sheet, so the following CODE example would be ok. However, if the ranges are on separate sheets, then you would need to insert a row prior to each of the Range("__").Select rows where the inserted row would select the Worksheet... e.g. Worksheets("Parts_a").Select - if the "pa" range was on a sheet named "Parts_a".

Sub Sort_Ranges()
    Application.ScreenUpdating = False
    
    'Sort range "pa"
    'Worksheets("Parts_a").Select 'example re above note
    Range("pa").Select
    ActiveCell.Offset(0, 10).Name = "sort_1"
    ActiveCell.Offset(0, 5).Name = "sort_2"
    Selection.Sort _
    Key1:=Range("sort_1"), Order1:=xlAscending, _
    Key2:=Range("sort_2"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    
    'Sort range "pb"
    Range("pb").Select
    ActiveCell.Offset(0, 10).Name = "sort_1"
    ActiveCell.Offset(0, 5).Name = "sort_2"
    Selection.Sort _
    Key1:=Range("sort_1"), Order1:=xlAscending, _
    Key2:=Range("sort_2"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    
    'Sort range "pc"
    Range("pc").Select
    ActiveCell.Offset(0, 10).Name = "sort_1"
    ActiveCell.Offset(0, 5).Name = "sort_2"
    Selection.Sort _
    Key1:=Range("sort_1"), Order1:=xlAscending, _
    Key2:=Range("sort_2"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    
    'Sort range "pd"
    Range("pd").Select
    ActiveCell.Offset(0, 10).Name = "sort_1"
    ActiveCell.Offset(0, 5).Name = "sort_2"
    Selection.Sort _
    Key1:=Range("sort_1"), Order1:=xlAscending, _
    Key2:=Range("sort_2"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    
    
    Application.Goto Reference:="R1C1"
    Application.ScreenUpdating = True
End Sub

Hope this helps. ;-)

Regards, Dale Watson

 

[sedgely]

Thanks Guys

Dale used your code, worked great after a couple of tweaks.
Thanks again.

Cheers, Craig
Si fractum non sit, noli id reficere