Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109588 > unrolled thread

Multiple Sort Ranges via VBA Q

Back to article view | Back to microsoft.public.excel.programming

Contents

  Multiple Sort Ranges via VBA Q seanryanie@yahoo.co.uk - 2016-11-16 06:05 -0800
    Re: Multiple Sort Ranges via VBA Q Claus Busch <claus_busch@t-online.de> - 2016-11-16 15:18 +0100
      Re: Multiple Sort Ranges via VBA Q seanryanie@yahoo.co.uk - 2016-11-16 06:33 -0800
      Re: Multiple Sort Ranges via VBA Q seanryanie@yahoo.co.uk - 2016-11-16 10:56 -0800
        Re: Multiple Sort Ranges via VBA Q Claus Busch <claus_busch@t-online.de> - 2016-11-16 20:03 +0100
          Re: Multiple Sort Ranges via VBA Q seanryanie@yahoo.co.uk - 2016-11-16 12:02 -0800

#109588 — 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

[toc] | [next] | [standalone]

#109589

Hi Sean,

Am Wed, 16 Nov 2016 06:05:01 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> 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

[toc] | [prev] | [next] | [standalone]

#109591

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 seanryanie@yahoo.co.uk:
> 
> > 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

[toc] | [prev] | [next] | [standalone]

#109596

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

[toc] | [prev] | [next] | [standalone]

#109597

Hi Sean,

Am Wed, 16 Nov 2016 10:56:20 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> 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

[toc] | [prev] | [next] | [standalone]

#109598

Thanks Claus, that clarifies it

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web