View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] seanryanie@yahoo.co.uk is offline
external usenet poster
 
Posts: 73
Default Multiple Sort Ranges via VBA Q

Code below sorts 2 Range Names called "Sort1" & "Sort2". It uses Column I as the Sort field from Largest down

I have over 50 of these Ranges, is there anyway to short circuit the code below to include all 50 of these Sort Ranges?

Sub SortProductSales()

Application.Goto Reference:="Sort1"
ActiveWorkbook.Worksheets("Input").Sort.SortFields .Add Key:=Range("I15:I22") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Input").Sort
.SetRange Range("F15:I22")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.Goto Reference:="Sort2"
ActiveWorkbook.Worksheets("Input").Sort.SortFields .Add Key:=Range("I23:I30") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Input").Sort
.SetRange Range("F23:I30")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub