Dim RngToSort as Range
dim KeyCol as range

set rngtosort = nothing
on error resume next
set rngtosort = application.inputbox(Prompt:="Select the range to sort", _
Type:=8).areas(1)
on error goto 0

if rngtosort is nothing then
exit sub 'user hit cancel
end if

set keycol = nothing
on error resume next
set keycol = application.inputbox(Prompt:="Select a cell in that range!", _
type:=8).cells(1)
on error goto 0

if keycol is nothing then
exit sub 'user hit cancel
end if

if keycol.parent.range("a1").address(external:=true) _
< rngtosort.parent.range("a1").address(external:=tru e then
msgbox "Key and sort range have to be on the same worksheet!"
exit sub
end if

if intersect(keycol.entirecolumn, rngtosort) is nothing then
msgbox "Select a column in the range, please!"
exit sub
end if

rngtosort.sort key1:=keycol, Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Untested, uncompiled. Watch for typos.

You don't actually need to specify a row--just a column that's within the range
to sort.

How do you know that there are no headers in the selected range????????

===========
As an alternative, you may want to look at this from Debra Dalgleish's site:
http://contextures.com/xlSort02.html

It sorts a predefined set of columns by using invisible rectangles over the
header cells.



Tendresse wrote:

Hi all,

I have a worksheet that has 106 columns, some of them have dates, some have
text, etc. I want to write a macro to allow users to sort the list in
ascending order based on the column of their choice, by asking the user to
enter the letter of the alphabet located on top of the column. Im using
excel 2003. I dont seem to get the code right yet. I would appreciate it if
someone tells me where I went wrong:

Sub SORT()

€˜ code that will select the range to be sorted

€˜ Ask user which column they want to sort by

Dim ColumnToSort as String

ColumnToSort = InputBox (€œPlease enter the index of the column you wish to
sort by€�)

If ColumnToSort = €œ€� then
Exit Sub
End If

€˜Here is where Im stuck. I dont know how to use the ColumnToSort as the Key1
€˜I want Key1 to be the cell in Row number 13 and Column ColumnToSort

Selection.Sort Key1:=Range(ColumnToSort & 13), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End sub

--

Dave Peterson