Sort Order
 

So, I'm trying to sort a specific way. Here is what I have.

Cells.Sort Key1:=Range("C:C"), Order1:=x1Descending

Unfortunately, there are at least a dozen variables and there are certain ones I want at the top. Basically, i want to say the order it goes in - not ascending or descending. Is there a way to specifiy the order?

Thanks,

J

Sort Order
 

On Wed, 20 Mar 2013 12:08:01 +0000, JonathanK1 wrote:


So, I'm trying to sort a specific way. Here is what I have.

Cells.Sort Key1:=Range("C:C"), Order1:=x1Descending

Unfortunately, there are at least a dozen variables and there are
certain ones I want at the top. Basically, i want to say the order it
goes in - not ascending or descending. Is there a way to specifiy the
order?

Thanks,

J

You can use a custom list. In Excel 2007 or later:

Here's a snippet I use to sort using a custom list. In this case I am sorting horizontally to get columns in a desired order, but you can obviously modify it. I don't recall the method for earlier versions of Excel -- probably you need to explicitly add a custom list.

In my example, aCL is a one dimensional array that contains the items in sorted order, converted into a comma-separted list using the Join function, but CustomOrder can actually be any comma-separated list. (e.g: CustomOrder:= "ItemA","ItemC","ItemB","xxx" )

=====================
....
Set r = .UsedRange
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=r.Rows(1), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:=Join(aCL, ",")
With .Sort
.SetRange r
.Header = xlYes
.Orientation = xlLeftToRight
.Apply
End With
.......
=====================================

Then for my needs, shouldn't this work?

Cells.Sort Key1:=Range("C:C"), Order1:=xlDescending, CustomOrder:="67,08,47,41,25,03,7,6,23"

It doesn't though, for whatever reasons.

Sort Order
 

On Thu, 21 Mar 2013 11:45:29 +0000, JonathanK1 wrote:


Then for my needs, shouldn't this work?

Cells.Sort Key1:=Range("C:C"), Order1:=xlDescending,
CustomOrder:="67,08,47,41,25,03,7,6,23"

It doesn't though, for whatever reasons.

Works for me. Those values are at the top, other values sort below per excel sort rules.
Probably something about your data. If your customorder keys are a part of the string to be sorted, you may need to break them out into a helper column, and sort on that column. Don't forget to format as text so as to retain the leading zero's

Hmm...not entirely sure what you mean. I don't know anything about "helper columns" etc. Here is what I have:

Cells.Sort Key1:=Range("C:C"), Order1:=xlDescending,_
CustomOrder:="67,08,47,25,03,07,23"
Key2:=Range("Q:Q"), Order2:=xlDescending, Header:=xlYes
End Sub

Sort Order
 

On Fri, 22 Mar 2013 16:50:47 +0000, JonathanK1 wrote:


'Ron Rosenfeld[_2_ Wrote:
;1610526']On Thu, 21 Mar 2013 11:45:29 +0000, JonathanK1
wrote:
-

Then for my needs, shouldn't this work?

Cells.Sort Key1:=Range("C:C"), Order1:=xlDescending,
CustomOrder:="67,08,47,41,25,03,7,6,23"

It doesn't though, for whatever reasons.-

Works for me. Those values are at the top, other values sort below per
excel sort rules.
Probably something about your data. If your customorder keys are a part
of the string to be sorted, you may need to break them out into a helper
column, and sort on that column. Don't forget to format as text so as
to retain the leading zero's

Hmm...not entirely sure what you mean. I don't know anything about
"helper columns" etc. Here is what I have:

Cells.Sort Key1:=Range("C:C"), Order1:=xlDescending,_
CustomOrder:="67,08,47,25,03,07,23"
Key2:=Range("Q:Q"), Order2:=xlDescending, Header:=xlYes
End Sub

As I wrote, "it works for me. Probably something about your data."

For example, if I place the values that you have included in your custom sort list, into a column, in random order, and include other values, it will sort as I would expect it to. For example

Original data:
1
5
10
08
03
47
15
18
25
6
7
67

================================
Sub foo()
With Sheet1
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A1:A12"), _
Order:=xlAscending, SortOn:=xlSortOnValues, _
CustomOrder:="67,08,47,41,25,03,7,6,23"
With .Sort
.SetRange Range("A1:A12")
.Header = xlNo
.Apply
End With
End With
End Sub
=============================
After running the above macro:

67
08
47
25
03
7
6
1
5
10
15
18

The list is sorted by the custom order, for the values that are in the custom list (the first seven numbers), then they are sorted ascending.

Is that what you would expect to happen? Or are you expecting something else to happen?

In other words, there may be SOMETHING ABOUT YOUR DATA that is causing the sort to not be WHAT YOU EXPECT IT TO BE. Or perhaps your SECONDARY SORT COLUMN is changing the results from what you expect. Or maybe your expectations are not in accord with the Excel sorting algorithm.

Hard to tell from what you have posted so far.