Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to sort variable length list (Excel 2002/SP3)

    Hi all,

    I am trying to make a macro to sort a list. Therefore I recorded the keystrokes when sorting the list. But on looking at the code, I saw that the code contained the exact range :

    <font color=blue> Sub SortOnVtgw()
    '
    ' SortOnVtgw Macro
    ' Macro recorded 29/11/2004 by Walter
    '
    '
    Range("A1:E81"). Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers
    End Sub
    </font color=blue>

    But, as I want to use this macro on different sheets (with same fields but different number of rows), I would like to know how to tell the macro to use the (variable length) range. So "A1:E81" needs to be changed to something else, but what ?

    Somehow I feel that this one is an easy one for the "macro-goeroes", but I am stuck with it. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    MTIA

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to sort variable length list (Excel 2002/SP3)

    Sub SortOnVtgw()
    '
    ' SortOnVtgw Macro
    ' Macro recorded 29/11/2004 by Walter
    '
    Dim rng As Range
    Set rng = Range("A1").CurrentRegion
    rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub

    This code assumes your list starts in cell A1 (headers included)
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to sort variable length list (Excel 2002/SP3)

    Thanks you Hans,

    I used the Range("A1").CurrentRegion solution, and got my list sorted.

    BTW, in your alternate solution (working from the bottom of the sheet and move up) I don't understand the following code :
    <font color=blue> Range(Range("A1"), Range("E" & lngLastRow).Sort Key1:=Range("B2"), ... </font color=blue>
    Using the Range inside the Range puzzles me. Why not just use "A1" ? Could you explain this to me, please ?

    MTIA

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to sort variable length list (Excel 2002/SP3)

    Thank you for your answer, Rudi.

    As you might have seen, Hans just beat you with his solution.
    But you're pointing to the same code (albeit in a slightly adjusted way).

    Thanks anyway !

    Regards

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to sort variable length list (Excel 2002/SP3)

    Edited by HansV to correct code.

    If the table has blank rows and columns around it but no blank rows or columns within, you can replace Range("A1:E81") with Range("A1").CurrentRegion. the CurrentRegion of A1 is the largest rectangular area containing A1 without blank rows or columns.

    Another possibility is to start from the bottom of the worksheet and move up until you meet a non-blank cell. Say that column A will always be populated within the block to be sorted.

    Dim lngLastRow As Long
    lngLastRow = Range("A65536").End(xlUp).Row
    Range(Range("A1"), Range("E" & lngLastRow)).Sort Key1:=Range("B2"), ...

    If you want to sort a range on another worksheet, this becomes

    Dim wsh As Worksheet
    Dim lngLastRow As Long
    Set wsh = Worksheets("Sheet2")
    lngLastRow = wsh.Range("A65536").End(xlUp).Row
    wsh.Range(wsh.Range("A1"), wsh.Range("E" & lngLastRow)).Sort Key1:=wsh.Range("B2"), ...

    Note that ALL ranges used have been prefixed with wsh.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to sort variable length list (Excel 2002/SP3)

    Sorry, there was a typo - I omitted a closing parenthesis in both fragments of code. I have corrected this.

    Range(Range("A1"), Range("E" & lngLastRow)).Sort Key1:=Range("B2"), ...

    You can specify a range in different ways:

    Range("A1")
    Range("A1:B4")
    Range(cell1, cell2)
    In the last format, you can specify each of the cells in the form Range("A1"), so you get something like:

    Range(Range("A1"), Range("B4"))

    This may seem a roundabout way of doing it, but it allows you to be very flexible in the way you specify the cells.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •