With a cell selected in a bold header row, clcking the 'Sort Ascending'
button on the toolbar does exactly what I want.

It would be great to be able to put something this into a macro

Cells(2, 3). Sort, Order1:=xlAscending

but if I record a particular situation I get

ActiveCell.Offset(-1, 0).Range("A1:D75").Sort
Key1:=ActiveCell.Offset(-1, 1). _
Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

which is specific in that it is picking up the particular range which will
change in the sheet I am wanting to sort.

Any suggestions for a universal sorting macro - ok xlAscending and the other
bits can be set.

Francis Hookham

I've had excellent results with changing "Header:=xlGuess" to
"Header:=xlYes" .. since it's not a function I've needed to use often I
cannot tell you the specifics as to why it works better, but it does.


Francis Hookham wrote:
With a cell selected in a bold header row, clcking the 'Sort Ascending'
button on the toolbar does exactly what I want.

It would be great to be able to put something this into a macro

Cells(2, 3). Sort, Order1:=xlAscending

but if I record a particular situation I get

ActiveCell.Offset(-1, 0).Range("A1:D75").Sort
Key1:=ActiveCell.Offset(-1, 1). _
Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

which is specific in that it is picking up the particular range which will
change in the sheet I am wanting to sort.

Any suggestions for a universal sorting macro - ok xlAscending and the other
bits can be set.

Francis Hookham

Many thanks Tom, you have prompted me to simplifying it to

Sub SortDataArea()
SortRow = 3
SortCol = 1
Cells(SortRow, SortCol).CurrentRegion.Sort _
Key1:=Cells(SortRow, SortCol), Order1:=xlAscending, Header:=xlYes
End Sub

so I do not have to actually select an ActiveCell.

That will make a great macro for future use - I don't think it can be cut
down any more.

Francis Hookham

"Tom Ogilvy" wrote in message
...
ActiveCell.CurrentRegion.Sort _
Key1:=ActiveCell.Offset(0,1), _
Order1:=xlAscending
Header:=xlYes

(change Header to xlNo if it has a header)

this will work if your data is separated from all other date by at least
one
blank row/column in all directions and you want to use the column to the
right of the activecell as the sort key.

--
Regards,
Tom Ogilvy

"Francis Hookham" wrote:

With a cell selected in a bold header row, clcking the 'Sort Ascending'
button on the toolbar does exactly what I want.

It would be great to be able to put something this into a macro

Cells(2, 3). Sort, Order1:=xlAscending

but if I record a particular situation I get

ActiveCell.Offset(-1, 0).Range("A1:D75").Sort
Key1:=ActiveCell.Offset(-1, 1). _
Range("A1"), Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

which is specific in that it is picking up the particular range which
will
change in the sheet I am wanting to sort.

Any suggestions for a universal sorting macro - ok xlAscending and the
other
bits can be set.

Francis Hookham