Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Sort Ranges via VBA Q
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Sort Ranges via VBA Q
Thanks Claus, that clarifies it
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort and align 2 ranges | Excel Programming | |||
Sort 2 ranges with same Key | Excel Discussion (Misc queries) | |||
How do I sort by ranges? | Excel Discussion (Misc queries) | |||
Limiting Sort Ranges | Excel Discussion (Misc queries) | |||
Expanding ranges after sort??? | Excel Programming |