Sort Order

[JonathanK1]

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 ar
certain ones I want at the top. Basically, i want to say the order i
goes in - not ascending or descending. Is there a way to specifiy th
order?

Thanks,

 

[JonathanK1]

;1610490']On Wed, 20 Mar 2013 12:08:01 +0000, JonathanK

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 a
sorting horizontally to get columns in a desired order, but you ca
obviously modify it. I don't recall the method for earlier versions o
Excel -- probably you need to explicitly add a custom list.

In my example, aCL is a one dimensional array that contains the items i
sorted order, converted into a comma-separted list using the Joi
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

 

[Ron Rosenfeld]

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

 

[JonathanK1]

;1610526']On Thu, 21 Mar 2013 11:45:29 +0000, JonathanK

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 pe
excel sort rules.
Probably something about your data. If your customorder keys are a par
of the string to be sorted, you may need to break them out into a helpe
column, and sort on that column. Don't forget to format as text so a
to retain the leading zero's

Hmm...not entirely sure what you mean. I don't know anything abou
"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 Su

 

[Ron Rosenfeld]

;1610526']On Thu, 21 Mar 2013 11:45:29 +0000, JonathanK1

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.