Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Order? | Excel Discussion (Misc queries) | |||
Sort sheet tabs in alphabetical or numerical order | Excel Discussion (Misc queries) | |||
I would like to sort a work sheet based upon user input value,(dat | Excel Worksheet Functions | |||
I would like to sort a work sheet based upon user input value,(dat | Excel Worksheet Functions | |||
sort last name order | Excel Discussion (Misc queries) |