Excel 2010 A loop to Data Sort

Question

Hello from Steved

Data Sort

I use $ "Dollar"Values to sort in Column G:G ie $5.20, $11.70 and $2.70 and sort it to $2.70, $5.20 and $11.70

In row H:H I copy the numbers from E:E say 1 to 6, 1 to 15, 1 to 10,

so the column wil1 start with 1,2,3,4,5,6,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,1,2,3,4,5,6,7,8,9,10

0k looking at the above I want a script please that will only Data Sort the cells with say 1 to 6, or 1 to 15, or 1 to 10.

Meaning Data Sort 1 to 6, then Data Sort 1 to 15, and then Data Sort 1 to 10

So sorting the $ "Dollars" puts it from the  lowest to the Highest

I may have over 300 rows of this do to hence a macro that will loop please

The below does what I want, however I've got to highlite the "Range".

Sub DataSort()

    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("G2:G12") _

        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet3").Sort

        .SetRange Range("E2:G12")

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    Range("E2:H12").Select

    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("E2:E12") _

        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet3").Sort

        .SetRange Range("E2:H12")

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End Sub

Answer 1

The column with the sort order is defined by this line:

  Set Where = Columns("H")

The columns that should be sorted by this:

  Set Where = Intersect(Columns("E:G"), C.EntireRow)

And the column the has the sort values by this:

  Where.Sort Key1:=Intersect(Columns("G"), C.EntireRow), Header:=xlNo

Change the column names as you like.

Andreas.

Answer 2

Sub Test()

  Dim FirstAddress As String

  Dim Where As Range, C As Range

  Dim Stack As Object 'Dictionary

  Dim Temp

  Dim i As Long

  'Search all "1" in column H and store the cells into a dictionary

  Set Where = Columns("H")

  Set Stack = CreateObject("Scripting.Dictionary")

  Set C = Where.Find(1, LookIn:=xlValues, LookAt:=xlWhole)

  If C Is Nothing Then Exit Sub

  FirstAddress = C.Address

  Do

    Stack.Add Stack.Count, C

    Set C = Where.FindNext(C)

    If C Is Nothing Then Exit Do

  Loop Until FirstAddress = C.Address

  'Add the bottom cell too

  Set C = Where.Cells(Where.Cells.Count).End(xlUp).Offset(1)

  Stack.Add Stack.Count, C

  'Get all saved cells

  Temp = Stack.Items

  For i = 0 To UBound(Temp) - 1

    'Get the range 1..X

    Set C = Range(Temp(i), Temp(i + 1).Offset(-1))

    'Build the range to sort

    Set Where = Intersect(Columns("E:G"), C.EntireRow)

    'Sort it

    Where.Sort Key1:=Intersect(Columns("G"), C.EntireRow), Header:=xlNo

  Next

End Sub

Answer 3

Hello From Steved

Is it possible please to do a Key1 and Key2 Sort using the above "Test" macro

  'Build the range to sort

     Set Where = Intersect(Columns("C:G"), C.EntireRow)

     'Sort it

     Where.Sort Key1:=Intersect(Columns("G"), C.EntireRow), Header:=xlNo

  'Build the range to sort

     Set Where = Intersect(Columns("C:H"), C.EntireRow)

     'Sort it

     Where.Sort Key2:=Intersect(Columns("E"), C.EntireRow), Header:=xlNo

Thank you

Answer 4

Hello Andreas

Sorry I just was not focused I knew what had to be done as set out below.

From

The columns that should be sorted by this:

  Set Where = Intersect(Columns("E:G"), C.EntireRow)

To

The columns that should be sorted by this:

  Set Where = Intersect(Columns("F:G"), C.EntireRow)

Thanks again for your help sorry

Steved

Answer 5

Hello Andreas Killer

Thank you very much,

Yes you have done what I have asked. If I may I would like to make A change Please

Column E:E Gives me the sort order I require can we put that information in Colum H:H

Columns E:E and F:F stay as they were please before we started the macro

Otherwise all is Good

Thanks again for time on this.

Steved