Excel Sort Code

[sjh]

Hi,
I got the following code by recording a macro. Is it possible to dynamically assign the range values for Key1:=Range("E20&quot? i.e. I would like to change E20 value dynamically. If so, what is the correct syntax? Please help! I am stuck!

Range("A20:U94&quot.Sort Key1:=Range("E20&quot, Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[groston]

First, you need to tell us a little more: how is your macro to know the correct range to use? Must it scan the sheet looking for cells with values? Is the user going to click a button?

 

[SkipVought]

what do you want to use to dynamically assign?

a heading name?

A column number?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

I am going to use the sort code in the command button click event. In the excel sheet, users can specify the sort order of the columns, so from this I get which range to use. I hope this clarifies things.

 

[sjh]

To Skip:

Both column number and heading name are fine. I just don't know the correct syntax for specifying either of those. Please help! Much thanks...

 

[SkipVought]

    iCol = 5
    Range("A20:U94").Sort Key1:=Cells(20, iCol),Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

should work

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

To Skip:

Thanks for your post! It worked successfully. I tried to do the same for the Sort Order, but I get <Sort method of range class failed> error.

Here's my code.
sSortOrder = &quot;xlAscending&quot;
iCol = 5
Range(&quot;A20:U94&quot.Sort Key1:=Cells(20, iCol),Order1:=sSortOrder, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[SkipVought]

xlAscending is NOT text!

assign it to a variant

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

Hello again,

How should I change the sort code, if I wanted to sort the fields in a different sheet? I tried the following, but I get an error.

Range(mySheet.Cells(1, 9), mySheet.Cells(9, 20)).Sort Key1:=mySheet.Range(&quot;A10&quot, Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Thank you~