Hi roger,
your suggestion runs without error but does NOTactually sort.
Any further suggestions?

"Roger Govier" wrote:

Hi Helmut

Try
Sub test()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", [A11], , , xlByColumns, xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A11], , , xlByRows, xlPrevious).Row

Range("A11").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(11, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(11, 1), Cells(lr, lc))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

--

Regards
Roger Govier

"Helmut" wrote in message
...
I tried but the "usedrange" doesn't work...I changed the lines:

Range("A11").Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add
Key:=usedrange _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With Activesheet.Sort
.SetRange usedrange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"carlo" wrote:

instead of activeworkbook.worksheets(bla) you could use activesheet
which would take the sheet that has been activated the last as
reference.

and if you want to sort the whole used area you could use usedrange as
range.

If that doesn't help, try to give some more information.

Carlo

On Jan 9, 4:41 pm, Helmut wrote:
How can I make the following work on any worksheet and on whatever the
new
range might be "A11:Axxx" - "A11:Jxxx"

Range("A11").Select
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.C lear
ActiveWorkbook.Worksheets("JAN").Sort.SortFields.A dd
Key:=Range("A11:A68") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("JAN").Sort
.SetRange Range("A10:J68")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With