Sort sheet based on particuilar sort order
 

Slightly odd one.
I want to sort a sheet in order of, for example, "Grape, Apple, Orange,
Banana, Melon".
Of course at the moment it will just sort alphabetically.
I can use the CustomList option BUT this spreadsheet needs to be shared with
others and I'm still on Excel 2000...

The sort it self will be called by a Macro:

Sheets("Fruit").Select
Rows("5:225").Select
Selection.Sort Key1:=Range("X6"), Order1:=xlAscending, Header:=xlYes,
OrderCustom:XXXX, MatchCase:=False, Orientation:=xlTopToBottom

Where "Fruit" is the sheet, 5:225 are the rows to be sorted and X6 has the
Melon, Grape etc.
Of course using Custom Lists on my machine XXXX = 8. But on my colleagues
they don't have the custom list and I would prefer not to add it if possible
but to keep the sort order in the Macro.
(The list I want to sort by is at:
Sheets("Types").Range("B54:B60")
Any ideas? I tried subbing it in and it didn't like it...

Thanks and Happy New Year in advance.

Sort sheet based on particuilar sort order
 

You might consider using a VLOOKUP to get unique sort numbers into a helper
column and then sorting by that column, then deleting it......all by macro

Vaya con Dios,
Chuck, CABGx3



"Also" wrote:

Slightly odd one.
I want to sort a sheet in order of, for example, "Grape, Apple, Orange,
Banana, Melon".
Of course at the moment it will just sort alphabetically.
I can use the CustomList option BUT this spreadsheet needs to be shared with
others and I'm still on Excel 2000...

The sort it self will be called by a Macro:

Sheets("Fruit").Select
Rows("5:225").Select
Selection.Sort Key1:=Range("X6"), Order1:=xlAscending, Header:=xlYes,
OrderCustom:XXXX, MatchCase:=False, Orientation:=xlTopToBottom

Where "Fruit" is the sheet, 5:225 are the rows to be sorted and X6 has the
Melon, Grape etc.
Of course using Custom Lists on my machine XXXX = 8. But on my colleagues
they don't have the custom list and I would prefer not to add it if possible
but to keep the sort order in the Macro.
(The list I want to sort by is at:
Sheets("Types").Range("B54:B60")
Any ideas? I tried subbing it in and it didn't like it...

Thanks and Happy New Year in advance.

Sort sheet based on particuilar sort order
 

Thanks for the suggestion, but not what I'm after.

I don't want to use a Helper column if at all possible.
It would be an easy way out- but I want something I can then use on other
things without having to add multiple columns etc.


"CLR" wrote:

You might consider using a VLOOKUP to get unique sort numbers into a helper
column and then sorting by that column, then deleting it......all by macro

Vaya con Dios,
Chuck, CABGx3



"Also" wrote:

Slightly odd one.
I want to sort a sheet in order of, for example, "Grape, Apple, Orange,
Banana, Melon".
Of course at the moment it will just sort alphabetically.
I can use the CustomList option BUT this spreadsheet needs to be shared with
others and I'm still on Excel 2000...

The sort it self will be called by a Macro:

Sheets("Fruit").Select
Rows("5:225").Select
Selection.Sort Key1:=Range("X6"), Order1:=xlAscending, Header:=xlYes,
OrderCustom:XXXX, MatchCase:=False, Orientation:=xlTopToBottom

Where "Fruit" is the sheet, 5:225 are the rows to be sorted and X6 has the
Melon, Grape etc.
Of course using Custom Lists on my machine XXXX = 8. But on my colleagues
they don't have the custom list and I would prefer not to add it if possible
but to keep the sort order in the Macro.
(The list I want to sort by is at:
Sheets("Types").Range("B54:B60")
Any ideas? I tried subbing it in and it didn't like it...

Thanks and Happy New Year in advance.

Sort sheet based on particuilar sort order
 

Maybe you can add the customlist, use it and delete it all in code:

Option Explicit
Sub testme01()

Dim myArr As Variant
Dim myListNumber As Long

myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon")
Application.AddCustomList listarray:=myArr
myListNumber = Application.GetCustomListNum(myArr)

Sheets("Fruit").Select
Rows("5:225").Select

Selection.Sort Key1:=Range("X6"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=myListNumber + 1

Application.DeleteCustomList myListNumber

End Sub

Untested, uncompiled. Watch for typos.


Also wrote:

Slightly odd one.
I want to sort a sheet in order of, for example, "Grape, Apple, Orange,
Banana, Melon".
Of course at the moment it will just sort alphabetically.
I can use the CustomList option BUT this spreadsheet needs to be shared with
others and I'm still on Excel 2000...

The sort it self will be called by a Macro:

Sheets("Fruit").Select
Rows("5:225").Select
Selection.Sort Key1:=Range("X6"), Order1:=xlAscending, Header:=xlYes,
OrderCustom:XXXX, MatchCase:=False, Orientation:=xlTopToBottom

Where "Fruit" is the sheet, 5:225 are the rows to be sorted and X6 has the
Melon, Grape etc.
Of course using Custom Lists on my machine XXXX = 8. But on my colleagues
they don't have the custom list and I would prefer not to add it if possible
but to keep the sort order in the Macro.
(The list I want to sort by is at:
Sheets("Types").Range("B54:B60")
Any ideas? I tried subbing it in and it didn't like it...

Thanks and Happy New Year in advance.

--

Dave Peterson

Sort sheet based on particuilar sort order
 

Thanks,
This worked great- I was half way towards doing this but wasn't sure on the
codes having read by pure chance the Excel warning about the GetCustomListNum
being out by one- a bug in Excel since version 4 and still not fixed 3
versions and several years on IIRC!

For anyone else reading this helpful code the only thing to watch out for is
that if you ALREADY have this list as a Custom List on your machine it will
DELETE IT!
My suggestion would be to add in an extra sort option- e.g. if it were
weekdays:
"Mon, Tue, Wed, Thu, Fri, Sat, Sun, Placebo"
So that the code will delete only this list and not the list you had added
yourself and use!:
"Mon, Tue, Wed, Thu, Fri, Sat, Sun"

NOTE THIS EXAMPLE USES A DEFAULT LIST WHICH THANKFULLY CAN'T BE DELETED!

I hope that makes sense/helps anyone else who trawls these forums like I do
for answers- and thanks to anyone reading this who also thought about
posting- it's a huge resource which frankly our IT department can't
understand what I am doing half the time!

Thanks again and Happy New Year.