Sort Dynamic Range

[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

 

[Dave Peterson]

Can you use a column in your data to determine the last row to sort?
Can you use a row in your data to determine the last column to sort?

Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet
'I'm using column A to determine the last row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'and row 4 to determine the last column
LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Row

With .Range("A4", .Cells(LastRow, LastCol))
.Cells.Sort _
Key1:=.Columns(1), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With
End With

=======
I sorted by the first column (column A) and I guessed that you had a header.
It's your data. It's better if you specify it than let excel guess.

 

[meh2030]

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 togive
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

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("A410")
Set rngSortKey = Range("A4")

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

End Sub

 

[lightjag]

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("A410")
Set rngSortKey = Range("A4")

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

End Sub

Matt, with regard to your solution:

"Set rngSort = Range("A410")" <== this is static, how would I make it
dynamic (as # of row & col will chg)?

 

[meh2030]

Matt, with regard to your solution:

"Set rngSort = Range("A410")"  <== this is static, how would I make it
dynamic (as # of row & col will chg)?- Hide quoted text -

- Show quoted text -

Lightjag,

There are two issues you need to consider, assuming that your data is
increasing in row or column size: (1) how the data set expands, and
(2) how to set the Key1 argument of the Sort Method.

(1) If your sort range is contiguous, then there needs to be a way to
access at least one cell of the dynamic range. If you can find one of
these cells, then expanding your range to the contiguous cells in the
data set will be relatively easy.

(2) There needs to be a consistent key. For example, maybe you always
sort by the left most column, or maybe the right most column, or maybe
one to the right of the left most column, etc.

Dave's response has one method of making your range dynamic. It uses
the .End property (in Excel this is equivalent to Ctrl + Arrow
Keys); .End moves through contiguous cells. You can also accomplish
this through .CurrentRegion (see VBE help).

I've provided an example of an imaginary data set that has the upper-
left corner of the data always in C10, and sorts according to the
right most column of the data set. Again, feel free to get your
"anchor" cell in a way that fits your procedure (e.g. InputBox, Loop
through a row or column, etc.). If you provide more detail regarding
the positioning of your data then we can help you narrow down how to
make it dynamic for what you are doing.

Best,

Matt

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

Set rngSort = Range("C10").CurrentRegion

Set rngSortKey = rngSort.Cells(1, 1)
Set rngSortKey = Cells(rngSortKey.Row, rngSortKey.Column _
+ rngSort.Columns.Count - 1)

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

End Sub