L
lightjag
Sort Dynamic Range
Issue:
1) I have a dynamic data range, (i.e. # of rows and cols may vary).
2) example range: A410
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.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.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
Issue:
1) I have a dynamic data range, (i.e. # of rows and cols may vary).
2) example range: A410
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.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.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