sorting range

[Guest]

hi,
I'm using the following code to sort a selected range based on the columns,
C,B and F.I don't know why the range is sorted only based on Column C?
Sub Rectangle1954_Click()
Dim myRng As Range
Set myRng = Selection.Areas(1)
With myRng
.Sort key1:=Columns("C"), order1:=xlAscending, header:=xlNo,
MatchCase:=False, Orientation:=xlTopToBottom, key2:=Columns("B"),
order1:=xlAscending, header:=xlNo, MatchCase:=False,
Orientation:=xlTopToBottom, key3:=Columns("F"), order1:=xlAscending,
header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

any help?
thanx

 

[Guest]

The code wil onluy sort the selected area. try this test code and see what
is selected after it runs

sub test()

Areas(1).select

end sub

The area that is selected is the area that is sorted in you code.

 

[Guest]

it's fine.I have no problem in Area that's selected.I need to know why it
only sorts the first "key" no matter which one is first.

 

[Dave Peterson]

Your sorting by column C of the activesheet--not the 3rd column of the first
area.

Maybe...

With myRng
.Sort key1:=.Columns(3), order1:=xlAscending, _
key2:=,Columns(2), order2:=xlAscending, _
key3:=.Columns(6), order3:=xlAscending, _
header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom
End With

You may want to check to see if that Area has at least 6 columns, too:

if myrng.columns.count < 6 then
'error message or resize or ????

 

[Chip Pearson]

There are quite a number of problems with your code. First of all, I wonder
if you really mean Selection.CurrentRegion rather than Selection.Areas(1),
but that's up to you and your data. Next, the parameters to the Sort method
are all screwed up. Simplify to the code below. You'll find that the code is
much easier to read and maintain if you split long lines into single lines,
using a '_' character as a line continuation character. I tend to split
lines to one parameter per line.

Dim myRng As Range
Set myRng = Selection.Areas(1)
With myRng
.Sort _
key1:=Columns("C"), _
order1:=xlAscending, _
key2:=Columns("B"), _
order2:=xlAscending, _
key3:=Columns("F"), _
order3:=xlAscending, _
header:=xlNo, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With

You had multiple arguments for Header, Orientation, MatchCase and Order1,
while at the same time not having Order2 or Order3. I'm actually surprised
your code even compiled with duplicated named arguments. Finally, you need
to ensure that the selected area contains columns B, C and F. If you select
a single cell within the range to be sorted, only that one cell will be
sorted, which basically means nothing happens.

Cleaning up the code in to a nice indented readable form makes many error
very easy to find.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

thanx.got it.

There are quite a number of problems with your code. First of all, I wonder
if you really mean Selection.CurrentRegion rather than Selection.Areas(1),
but that's up to you and your data. Next, the parameters to the Sort method
are all screwed up. Simplify to the code below. You'll find that the code is
much easier to read and maintain if you split long lines into single lines,
using a '_' character as a line continuation character. I tend to split
lines to one parameter per line.

Dim myRng As Range
Set myRng = Selection.Areas(1)
With myRng
.Sort _
key1:=Columns("C"), _
order1:=xlAscending, _
key2:=Columns("B"), _
order2:=xlAscending, _
key3:=Columns("F"), _
order3:=xlAscending, _
header:=xlNo, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With

You had multiple arguments for Header, Orientation, MatchCase and Order1,
while at the same time not having Order2 or Order3. I'm actually surprised
your code even compiled with duplicated named arguments. Finally, you need
to ensure that the selected area contains columns B, C and F. If you select
a single cell within the range to be sorted, only that one cell will be
sorted, which basically means nothing happens.

Cleaning up the code in to a nice indented readable form makes many error
very easy to find.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)