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

Multiple Sort Ranges via VBA Q
 

Hi Sean,

Am Wed, 16 Nov 2016 06:05:01 -0800 (PST) schrieb :

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?

if the ranges have always 7 rows you could do it with a loop:

Sub SpecialSort()
Dim i As Long, LRow As Long

With Sheets("Input")
LRow = .Cells(.Rows.Count, "I").End(xlUp).Row
For i = 15 To LRow Step 8
.Range("F" & i & ":I" & i + 7).Sort Key1:=Range("I" & i), _
order1:=xlDescending, Header:=xlNo
Next
End With
End Sub

Else you could write all ranges in an array and then step through this
array.


Regards
Claus B.
--
Windows10
Office 2016

Multiple Sort Ranges via VBA Q
 

On Wednesday, November 16, 2016 at 2:18:08 PM UTC, Claus Busch wrote:
Hi Sean,

Am Wed, 16 Nov 2016 06:05:01 -0800 (PST) schrieb :

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?

if the ranges have always 7 rows you could do it with a loop:

Sub SpecialSort()
Dim i As Long, LRow As Long

With Sheets("Input")
LRow = .Cells(.Rows.Count, "I").End(xlUp).Row
For i = 15 To LRow Step 8
.Range("F" & i & ":I" & i + 7).Sort Key1:=Range("I" & i), _
order1:=xlDescending, Header:=xlNo
Next
End With
End Sub

Else you could write all ranges in an array and then step through this
array.


Regards
Claus B.
--
Windows10
Office 2016


Claus, very clever, yes all have 7 rows, I'll try above, thanks

Multiple Sort Ranges via VBA Q
 

Claus, that's 7 rows after the first row in the range, correct? So 8 rows in each Range

Multiple Sort Ranges via VBA Q
 

Hi Sean,

Am Wed, 16 Nov 2016 10:56:20 -0800 (PST) schrieb :

Claus, that's 7 rows after the first row in the range, correct? So 8 rows in each Range

yes, that's correct. Therefore I wrote:
For i = 15 To LRow Step 8

The first range is I15:I22.
The next range is I23:I30
and so on


Regards
Claus B.
--
Windows10
Office 2016

Multiple Sort Ranges via VBA Q
 

Thanks Claus, that clarifies it