Sort sheet based on particuilar sort order

[Also]

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.

 

[CLR]

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]

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.

 

[Dave Peterson]

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]

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.