Page 1 of 2 12 LastLast
Results 1 to 10 of 14

VBA code to sort a variable length range

This is a discussion on VBA code to sort a variable length range within the Excel Questions forums, part of the Question Forums category; I am new to VBA and am trying to use the macro recorder to create the code to sort a ...

  1. #1
    New Member
    Join Date
    May 2009
    Posts
    4

    Default VBA code to sort a variable length range

    I am new to VBA and am trying to use the macro recorder to create the code to sort a range which can have a variable number of rows. I have tried using the relative addressing mode when recording the procedure, but the resulting code always uses the original range, ignoring additional rows when present. The macro recorder code is as follows:

    Sub TestSort()
    '
    ' TestSort Macro
    '
    ' Keyboard Shortcut: Ctrl+s
    '
    Range("E2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=ActiveCell. _
    Offset(0, 1).Range("A1:A17155"), SortOn:=xlSortOnValues, Order:=xlAscending _
    , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
    .SetRange ActiveCell.Offset(-1, 0).Range("A1:R17156")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Range("A1").Select
    End Sub

    It seems that the range for sorting should have a relative addressing format, but it just "hard codes" the range that I used when doing the original macro recording.

    I'm sure that I am missing something basic here, but I am stuck.

    ChartMaster

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,058

    Default Re: VBA code to sort a variable length range

    Using a dynamic range name as the address might do the trick:
    http://www.contextures.com/xlNames01.html


    -----------------------
    Note: This typically works best when the data in the dynamic range is "packed" - no blanks anywhere. To refer to it in code you can probably just record again, but also, if A1:A10 is named MyRange, these are equivalent:

    Range("A1:A10")
    Range("MyRange")

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    May 2009
    Posts
    4

    Default Re: VBA code to sort a variable length range

    I did try using a named range in the macro, but it didn't work. I will try using the naming formula that you suggest to see if that works. I am assuming that I would set up that range name outside the macro and just refer to the range name in the sort commands, is that correct?

    ChartMaster

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,058

    Default Re: VBA code to sort a variable length range

    Yes. Define the named range first, then use it in your macro. Let us know how you come along here.

    Alex

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    New Member
    Join Date
    May 2009
    Posts
    4

    Default Re: VBA code to sort a variable length range

    I tried the suggestion, but still have the same problem of getting the macro to use the dynamic range definition as the range to sort. It still uses the range from the first sort that was done. I tried entering the dynamic range name into the vba sort routine as follows, but it wouldn't accept it and stopped with an error ("AddrRange" is the name I assigned to the dynamic range):


    Application.Goto Reference:="AddrRange"
    ActiveWorkbook.Worksheets("Std Addresses").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Std Addresses").Sort.SortFields.Add Key:=Range( _
    "AddrRange"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Std Addresses").Sort
    .SetRange Range("AddrRange")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub

    Any other thoughts?

  6. #6
    Board Regular
    Join Date
    Aug 2008
    Posts
    505

    Default Re: VBA code to sort a variable length range

    Heres a snippet of code that I use to sort a variable row range (Excel 2003)

    NumberOfRows = Sheets("sheet3").Range("m65536").End(xlUp).Row


    Range(Cells(2, 13), Cells(NumberOfRows, 16)).Sort Key1:=Range("P9"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Maybe something like this will help?
    Last edited by nigelk; May 24th, 2009 at 04:55 PM.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,058

    Default Re: VBA code to sort a variable length range

    nigel's code looks good too. If you like, give it a go. If interested in pursuing dynamic range names a little more, re-record your code and post it here. Also post the results of this code:

    Code:
    Sub foo()
    MsgBox Worksheets("Std Addresses").Range("AddrRange").Address
    End Sub
    (Note: When reporting errors, always (if possible) give the error number, error description, and the line of code the error occurs on.)

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    New Member
    Join Date
    May 2009
    Posts
    4

    Default Re: VBA code to sort a variable length range

    Quote Originally Posted by Alexander Barnes View Post
    nigel's code looks good too. If you like, give it a go. If interested in pursuing dynamic range names a little more, re-record your code and post it here. Also post the results of this code:

    Code:
    Sub foo()
    MsgBox Worksheets("Std Addresses").Range("AddrRange").Address
    End Sub
    (Note: When reporting errors, always (if possible) give the error number, error description, and the line of code the error occurs on.)
    Thanks to both of you for your help. I think I have something now that is working. I haven't completed the procedure yet, but it looks like it will work. Thanks!

  9. #9
    New Member
    Join Date
    May 2012
    Posts
    2

    Cool Re: VBA code to sort a variable length range

    Sub SortMyData()
    ' This is macro sorts a Dynamic data area that starts from Col A1 to any other Column.
    ' The Row & Column selection is Dynamic as the file could be 1000 rows today and 800
    ' rows tomorrow and 1200 the next day
    ' It sorts field in Column R and sorts in descending sequence
    ' Row 1 contains Headers and the data is in Sheet1 of the spreadsheet
    '
    '******************************* Define variables for the data that I want to store for later use
    Dim MyDataFirstCell
    Dim MyDataLastCell
    Dim MySortCellStart
    Dim MySortCellEnd

    Windows("CustomerOrdersGreaterThanFC.xlsx").Activate ' Go to my data file

    '************************** Establish the Data Area
    Range("A1").Select 'Get to the first cell of data area
    MyDataFirstCell = ActiveCell.Address 'Get the first cell address of Data Area
    Selection.End(xlDown).Select 'Get to Bottom Row of the data
    Selection.End(xlToRight).Select 'Get to the last Column and data cell by heading to the righthand end
    MyDataLastCell = ActiveCell.Address 'Get the Cell address of the last cell of my data area

    '************************** Establish the Sort column first and last data points.
    Range("R2").Select 'Get to first cell of data sort Column (Example Col 'R' Row 2 becuase Row 1 contains the header)
    MySortCellStart = ActiveCell.Address 'Get the Cell address of the first cell of my data sort Column
    Selection.End(xlDown).Select 'Get to the bottom Row of data
    MySortCellEnd = ActiveCell.Address 'Get the Cell address of the last cell of my sort Column



    '************************** Start the sort by specifying sort area and columns
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
    Key:=Range(MySortCellStart & ":" & MySortCellEnd), SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range(MyDataFirstCell & ":" & MyDataLastCell)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    End Sub

  10. #10
    New Member
    Join Date
    Jan 2013
    Posts
    5

    Default Re: VBA code to sort a variable length range

    here you guys go :x

    Code:
    Function LastRow()
        Dim rowfind As Long
        rowfind= WorksheetFunction.CountA(Range("A:A"))
        LastRow = rowfind
    End Function
    
    Function LastColumn()
        Dim columnfind As Long
        columnfind = WorksheetFunction.CountA(Range("1:1"))
        LastColumn = columnfind
    End Function
    
    Sub sortyness()
    Dim Sorton, sortdata
    
        Sorton = "A1:" & Cells(LastRow, 2).Address(0, 0) 'this one be like A1 to A something last
    
        sortdata = "A1:" & Cells(LastRow, LastColumn)Address(0, 0) 'this one be like A1 to last row and column
        
        ActiveWorkbook.Worksheets("Tabell").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Tabell").Sort.SortFields.Add _
        Key:=Range(Sorton), Sorton:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Tabell").Sort
            .SetRange Range(sortdata)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    I had to rewrite for forum it seeing i use a different language but i think the people searching for a formula like this will understand it.

    Sorton is basicly what column your sorting by, and sortdata is the whole thing whit headers.

    You dont have to use those functions alone, you could just put them inside the sorting formula unless your gona use them elsewhere.. i keep them split cus theyr usefull for other stuff.. like filling comboboxes and listboxes

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com