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

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.

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