Macro to Sort

[Tendresse]

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. I’m using
excel 2003. I don’t 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 I’m stuck. I don’t 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

 

[Barb Reinhardt]

Try Cells(13,ColumnsToSort)

 

[Dave Peterson]

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:=true 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.

 

[JLGWhiz]

If you are going to use Range() as your Sort Key then you would need to get
the column letter in the input box Like:

ColumnToSort = InputBox (“Please enter the index of the column you wish to
sort byâ€)
ColumnToSort = UCase(ColumnToSort)
SortKeyRange = ColumnToSort & 13
Selection.Sort Key1:=Range(SortKeyRange), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Otherwise, use the Cells configuration that Barb suggested. If you don't
want rows 1 thru 12 included in the sort, then you will need to define the
sort range as well. Otherwise, you can get undesired results.


If ColumnToSort = Ҡthen

 

[Tendresse]

Dave, thanks for your prompt reply. From what i can see, you are letting
users select the range to be sorted .. well i already know the range and i
have only one line in the code that selects it without having to involve the
user in this process. It's simply:

Range("A13:Corner").Select

and i know that row 13 is not the header of the table .. so this is also
sorted.
All i need from the user is to enter the index of the column they want to
sort by. That's all.
I see that you are asking the user to select a cell in the column they want
to sort by. But i already have the range selected ("A13:Corner"). Selecting
another range will deselct the range that needs to be sorted.
Rather than asking the user to select a cell within the range, i'm still
more inclined to ask them to simply type the letter that refers to the column
(the letter at the very top end of each column). and then use this letter
somehow as the KEY.

I'm heading to work now and i'll try Barb's solution and see if it will work.
Please don't think i'm just being stubborn .. i'm only trying to choose the
simpler option. Please let me know if from my answer you can see that i'm
still missing something ... or if you can see that my method in solving this
code would still leave some loose ends. You are the expert and i totally
trust your expertise.

 

[Dave Peterson]

Replace this:

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

with
set rngtosort = activesheet.range("a13:x99")

I don't know what a13:corner means.

 

[Tendresse]

Thank you all for your help ..

Barb: your suggestion worked perfectly .. thanks heaps ..

Dave: Range("A13:Corner") is the range i want to sort. I just defined a name
'Corner' to cell DB50 as the range will change every time the user adds new
rows (or delete existing rows).

JLGWhiz: I tried your solution but for some reason the macro doesn't like
this "&" symbol! Everytime i run it, this symbol gets highlighted and i get
an error message "Type Mismatch" .. any idea why this happens?