none
Range.Sort in Excel 2007 Macro

    Question

  • I have been killing myself trying to get Range.Sort to Function in a Macro.  I have the Range set and selected.  Then I am trying the following:

     

        rng = Range(sStartColumn & iTopRow & ":" & sEndColumn & iBottomRow).Select
       
        rng.Sort Key1 = Range("G2"), Order1 = xlAscending, Key2 = Range("E2"), Order2 = xlAscending, _
            Key3 = Range("M2"), Order3 = xlAscending, Orientation = xlSortColumns, _
            Header = xlYes

    I have tried a hundred variations of the above lines.  Does anyone have a thought on this that will save my sanity?

     

    Thanks in advance,

    Dave

    Thursday, May 08, 2008 11:24 PM

Answers

  • Hi Dave

     

    Not sure of your expectations. Hope that the code throws some error

     

    Please check if the following works for you

     

    Code Snippet

    Sub Sort_For_Dave()

    Dim sStartColumn
    Dim iTopRow
    Dim sEndColumn
    Dim iBottomRow

    sStartColumn = "A"
    iTopRow = 1
    sEndColumn = "M"
    iBottomRow = 871

    Dim Rng As Range
    Dim sRange1 As String
    sRange1 = sStartColumn & iTopRow & ":" & sEndColumn & iBottomRow

    Set Rng = Worksheets("Sheet1").Range(sRange1)
       
        Rng.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("E2"), Order2:=xlAscending, _
            Key3:=Range("M2"), Order3:=xlAscending, Orientation:=xlSortColumns, _
            Header:=xlYes


    End Sub

     

     

    Cheers

    Shasur

    Friday, May 09, 2008 2:21 AM

  • Hi,

    Try the following modifications

    Code Snippet

        Set rng = Range(sStartColumn & iTopRow & ":" & sEndColumn & iBottomRow)
        With rng
            .Sort Key1:=.Range("G2"), Order1:=xlAscending, _
                  Key2:=.Range("E2"), Order2:=xlAscending, _
                  Key3:=.Range("M2"), Order3:=xlAscending, Orientation:=xlSortColumns, _
                  Header:=xlYes
        End With

    • Marked as answer by cboshdave Friday, June 19, 2009 7:32 PM
    Friday, May 09, 2008 9:05 AM

All replies

  • Hi Dave

     

    Not sure of your expectations. Hope that the code throws some error

     

    Please check if the following works for you

     

    Code Snippet

    Sub Sort_For_Dave()

    Dim sStartColumn
    Dim iTopRow
    Dim sEndColumn
    Dim iBottomRow

    sStartColumn = "A"
    iTopRow = 1
    sEndColumn = "M"
    iBottomRow = 871

    Dim Rng As Range
    Dim sRange1 As String
    sRange1 = sStartColumn & iTopRow & ":" & sEndColumn & iBottomRow

    Set Rng = Worksheets("Sheet1").Range(sRange1)
       
        Rng.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("E2"), Order2:=xlAscending, _
            Key3:=Range("M2"), Order3:=xlAscending, Orientation:=xlSortColumns, _
            Header:=xlYes


    End Sub

     

     

    Cheers

    Shasur

    Friday, May 09, 2008 2:21 AM

  • Hi,

    Try the following modifications

    Code Snippet

        Set rng = Range(sStartColumn & iTopRow & ":" & sEndColumn & iBottomRow)
        With rng
            .Sort Key1:=.Range("G2"), Order1:=xlAscending, _
                  Key2:=.Range("E2"), Order2:=xlAscending, _
                  Key3:=.Range("M2"), Order3:=xlAscending, Orientation:=xlSortColumns, _
                  Header:=xlYes
        End With

    • Marked as answer by cboshdave Friday, June 19, 2009 7:32 PM
    Friday, May 09, 2008 9:05 AM
  • I have been killing myself trying to get Range.Sort to Function in a Macro.  I have the Range set and selected.  Then I am trying the following:

     

        rng = Range(sStartColumn & iTopRow & ":" & sEndColumn & iBottomRow).Select
       
        rng.Sort Key1 = Range("G2"), Order1 = xlAscending, Key2 = Range("E2"), Order2 = xlAscending, _
            Key3 = Range("M2"), Order3 = xlAscending, Orientation = xlSortColumns, _
            Header = xlYes

    I have tried a hundred variations of the above lines.  Does anyone have a thought on this that will save my sanity?

     

    Thanks in advance,

    Dave


    I had a similar question, thanks Andy and Shasur for your input, which I used but modified some.   I didn't know how long the end range could be, what if it were more than 871 rows?  I knew the data would be between Col A and Col M, had headers, needed a primary and secondary sort, but never know how long it will be.   My solution was as follows:

    'Code Snippet

    Range("M1").Select
    Selection.End(xlDown).Select
    EndRng = ActiveCell.Address

    Set Rng = Range("A1:" & EndRng)
        With Rng
            .Sort Key1:=.Range("E1"), Order1:=xlAscending, _
                  Key2:=.Range("D1"), Order2:=xlAscending, Orientation:=xlSortColumns, _
                  Header:=xlYes
        End With

    Friday, August 13, 2010 8:46 PM
  • Thanks Tom. I like your snippet and have used it. It worked well.

    Regards Audamus.

    Tuesday, July 23, 2013 1:27 AM
  • it's easy, you just missed a sign ":"

    after Key1 it's := instead of =
    after Order1 it's := instead of =

    and do first Key and then Order, i you'd ask order to something to your child, the kid also would like to know what first!

    kind regards

    Monday, September 02, 2013 1:38 PM