VBA code to sort a variable length range

[ChartMaster]

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

[xenou]

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")

[ChartMaster]

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

[xenou]

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

Alex

[ChartMaster]

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?

[nigelk]

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?

[xenou]

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:

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.)

[ChartMaster]

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:

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!

[Puffdaddy]

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

[IndirectCell]

here you guys go :x

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