On Mar 21, 12:24*pm, lightjag
wrote:
Sort Dynamic Range

Issue: *
1) *I have a *dynamic data range, *(i.e. # of rows and cols may vary).
2) *example range: A4:D10
3) *Problem: *the sort function is static and not dynamic, I tried to give
it a name range but I get an error.

Current *Macro:

Sub test1()
'
' test1 Macro
'

'
* * ActiveWorkbook.Names("sortrange").Delete
* * Application.Goto Reference:="client1"
* * Range(Selection, Selection.End(xlDown)).Select
* * Range(Selection, Selection.End(xlToRight)).Select
* * ActiveWorkbook.Names.Add Name:="sortrange", RefersToR1C1:= _
* * * * ActiveWindow.RangeSelection.Address
' * * * *"=Sheet1!R4C1:R8C4"
* * ActiveWorkbook.Names("sortrange").Comment = ""
* * ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
* * ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=ActiveCell, _
* * * * SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
* * With ActiveWorkbook.Worksheets("Sheet1").Sort
* * * * .SetRange ActiveCell.Range("sortrange") * *<===ISSUE: needs to be
dynamic
* * * * .Header = xlGuess
* * * * .MatchCase = False
* * * * .Orientation = xlTopToBottom
* * * * .SortMethod = xlPinYin
* * * * .Apply
* * End With
* * Application.Goto Reference:="R1C1"
End Sub

Ligthjag,

Rather than using a Name to refer to your sort range, try using a
range object. I simply recorded a sort macro and then added my own
code to it. See below. (Also, I'm not familiar with .SetRange. I'm
using Office 2003 and the Object Browser doesn't have .SetRange as a
property).

Best,

Matt Herbert

Sub SortRange()
Dim rngSort As Range
Dim rngSortKey As Range

Set rngSort = Range("A4:D10")
Set rngSortKey = Range("A4")

rngSort.Sort Key1:=rngSortKey, Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub