I am using Excel 2010 and I have some "sort code" that is the result
of the macro recorder. Is there anything that I can use in place of
G2:G109 as the range address? The data can be more or less the next
time that I run the macro and therefore I'd rather not use a hard
coded range. I tried using things like xlDown and few other things
that I found from research but nothing really worked. Thanks very
much.

'sort
ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Add
Key:=Range( _
"G2:G109"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("My Calcs").Sort
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

hi John,

Set rng = ActiveWorkbook.Worksheets("My Calcs").Range("G2:G" & Range("G" & Cells.Rows.Count).End(xlUp).Row)

ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Add Key:=rng, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal



--
isabelle



Le 2012-01-21 15:15, John Menken a écrit :
I am using Excel 2010 and I have some "sort code" that is the result
of the macro recorder. Is there anything that I can use in place of
G2:G109 as the range address? The data can be more or less the next
time that I run the macro and therefore I'd rather not use a hard
coded range. I tried using things like xlDown and few other things
that I found from research but nothing really worked. Thanks very
much.

'sort
ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Add
Key:=Range( _
"G2:G109"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("My Calcs").Sort
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Thank you isabelle.
I tried your code but unfortunately it does not perform the sort.
It doesn't throw an error but it doesn't sort by the intended column
which is column G.
Thanks.

John
It is usually better to give the sort range a range name and address
the range name in your code; something like range("data") rather than
the cells. you may need to make it a dynamic range name so it will
expand if you add new data, assuming the addition of more data is what
causes the range to change. Alternatively, you could use a table
which will exand automatically when you add data.
I hope this helps.
Ken


On Jan 21, 3:25*pm, isabelle wrote:
hi John,

Set rng = ActiveWorkbook.Worksheets("My Calcs").Range("G2:G" & Range("G" & Cells.Rows.Count).End(xlUp).Row)

ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Add Key:=rng, _
* * SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

--
isabelle

Le 2012-01-21 15:15, John Menken a écrit :



I am using Excel 2010 and I have some "sort code" that is the result
of the macro recorder. Is there anything that I can use in place of
G2:G109 as the range address? The data can be more or less the next
time that I run the macro and therefore I'd rather not use a hard
coded range. I tried using things like xlDown and few other things
that I found from research but nothing really worked. Thanks very
much.

'sort
ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Clear
* * *ActiveWorkbook.Worksheets("My Calcs").Sort.SortFields.Add
Key:=Range( _
* * * * *"G2:G109"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
* * * * *xlSortNormal
* * *With ActiveWorkbook.Worksheets("My Calcs").Sort
* * * * *.SetRange Range("A1").CurrentRegion
* * * * *.Header = xlYes
* * * * *.MatchCase = False
* * * * *.Orientation = xlTopToBottom
* * * * *.SortMethod = xlPinYin
* * * * *.Apply
* * *End With

I understand the concept you are expressing, it's just when I use the
recorder to select all data before giving it a range names it places
the code as a cell addressed range name.
Do you know the code for selecting all the data without specifying a
range name?
Thanks.

John Menken submitted this idea :
I understand the concept you are expressing, it's just when I use the
recorder to select all data before giving it a range names it places
the code as a cell addressed range name.
Do you know the code for selecting all the data without specifying a
range name?
Thanks.

I believe Ken is referring to the extensive amount of cleanup you'll
need to do in the code generated by the macro recorder. This is normal
since the macro recorder is prone to create the most inefficient code
you'll ever imagine. Thus, in the code where you see the sort range
address, swap that out for your defined name for the sort range.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc