Need to revise this Sorting code a bit.

[havocdragon]

Hi all. This code works, but I know there has to be a better way to do it.
Right now it seems "crude" to me. 2 things that bug me are 1) not being able
to use set userrange solely by itself to set the range (If I do it pulls the
value from the cell not the range). Second, is I thought there was a way to
select a cell in a sheet in one line of code like
Sheets("Sheet1").Range("A1").select. I am using excel 2000 so maybe this
matters.

Here is my code:

defaultrange = Selection.Address
Set UserRange = Application.InputBox(Prompt:="Select Column to Sort by
as format X:X ", Title:="Sort", Default:=defaultrange, Type:=8)

Sheets("Sort").Select
Range("A1000") = UserRange.Address
Set SortRange = Range("A1000")
Sheets("Main Scorecard (All FSEs)").Select
Range("4:28").Copy
Sheets("Sort").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
Selection.Sort Key1:=Range(SortRange), Order1:=xlDescending
Selection.Copy
Sheets("Main Scorecard (All FSEs)").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False



It's this part that bugs me

Range("A1000") = UserRange.Address
Set SortRange = Range("A1000")
&
Selection.Sort Key1:=Range(SortRange), Order1:=xlDescending

I 'should' just use the input box, and use the range selected in that
inputbox in the sorting as UserRange.

Also, is there a way, someone could just type like the letter D in the
inputbox and have the sorting sort by that range?

 

[havocdragon]

Got Part of it now, I found a much more effective way and more userfriendly
way of obtaining the column the user wants

Dim testrange As String
Dim SortRangeX As String

testrange = InputBox(prompt:="Enter Column Letter", Title:="Sort")
SortRangeX = testrange & ":" & testrange

so now all you have to do is

selection.sort key1:=Range(SortRangeX), Order1:=xlDescending