Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sorting named ranges 1

Status
Not open for further replies.

sedgely

Technical User
Feb 21, 2002
406
GB
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
 
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.
 
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".

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

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

Cheers, Craig
Si fractum non sit, noli id reficere
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top