bug in sort routines

[rockhammer]

There is a bug in my very simple sort routines that does not occur
consistently - and therefore baffles me. Perhaps I'm missing something
obvious.

In a tab containing essentially columns of numbers (percents), I have a
label defined as follows: citus=$a$11:$s$147. My intention is to create a
bunch of essentially identical sort routines which will sort this citus
selection according firstly to one of the columns from L to R in descending
order, and then secondly column F also in descending order - ie, the pairs of
sort orders are L+F, M+F, N+F, etc. Here is what one such routine looks like:

Sub sort_ctius_3m()
Application.Goto Reference:="ctius"
Selection.Sort Key1:=Range("P11"), Order1:=xlDescending, Key2:=Range( _
"F11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("P9").Select
End Sub

This routine was originally created via the "record macro" feature and I
just copied & pasted numerous of them and changed the SortKey1 and the
Range().Select to the columns I needed for each. They all reside in the same
"module".

The bug is: these sort routines work some times (say initially when the file
is opened) and then at some point (and I have no clue when this point will
show up) the routines would sort all rows in the citus selection EXCEPT the
first row. And then for unknown reasons, after running these sort routines a
few more times, they would start to work properly again.

If someone can point out to me what I'm doing wrong that would be very much
appreciated. Thanks.

 

[Joel]

Try this code. I think the problem is either you were on the wrong worksheet
or excel didn't like sorting a named range. This should solve both problems.

Sub sort_ctius_3m()

Set sht = Range("ctius").Parent
SortRange = Range("ctius").Address(external:=True)
With sht
.Range(SortRange).Sort _
Key1:=.Range("P11"), _
Order1:=xlDescending, _
Key2:=.Range("F11"), _
Order2:=xlDescending, _
Header:=xlNo
End With
End Sub

 

[rockhammer]

Hi Joel, thanks for the pointers. The one thing that jumped out immediately
when reading your code is the header setting. Yours is "No" whereas the
record macro feature gave me "Guess". Changing Guess into No seems to solve
the problem. I guess we don't want excel to guess.

Thanks also for showing me a new way to code. I have tried it and your code
works too. I have not used the "with" construct before but will utilize that
in the future.