Code for Advanced Sort

[fugfug]

Hi,

I am trying to write a macro to do a custom sort. I have writen part of it which enters a custom list then it moves to the data I want to sort and it is supposed to sort it in the order of the custom list. My problem is I can't get the macro to choose my custom list which is at the bottom of the custom lists. Below is the code.

Sheets("Customers").Select
Range("A3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=12, MatchCase:=False, Orientation:=xlTopToBottom

Thanks.

nb. Using Excel 97

James

[Bob Phillips]

If you are creating the customlist on the fly, it will get added to the end.

Sheets("Customers").Select
Range("A3").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=Application.CustomListCount, MatchCase:=False,
Orientation:=xlTopToBottom

I advise deleting it a=fterwards as well

Application.DeleteCustomList Application.CustomListCount

--
HTH

Bob Phillips

"fugfug" <fugfug.1s3oyl_1121245555.5562@excelforum-nospam.com> wrote in
message news:fugfug.1s3oyl_1121245555.5562@excelforum-nospam.com...
>
> Hi,
>
> I am trying to write a macro to do a custom sort. I have writen part of
> it which enters a custom list then it moves to the data I want to sort
> and it is supposed to sort it in the order of the custom list. My
> problem is I can't get the macro to choose my custom list which is at
> the bottom of the custom lists. Below is the code.
>
> Sheets("Customers").Select
> Range("A3").Select
> Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=12, MatchCase:=False, Orientation:=xlTopToBottom
>
> Thanks.
>
> nb. Using Excel 97
>
> James
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=386709
>

[fugfug]

Thanks Bob. I see the bit you have changed in the code

OrderCustom:=Application.CustomListCount

In my code I had this = to 12 which I assume means twelth on the list. Does the customlistcount just count how many items in the custom list or does it do something different?

Thanks.

James

[fugfug]

I've just tried the new code, it does not quite work. It always chooses the custom list one from the bottom of the list........

James

[fugfug]

I've changed it to OrderCustom:=(Application.CustomListCount) + 1 and it seems to work, still a bit confused as to what customlistcount does.

James

[Tom Ogilvy]

Just for information:

http://support.microsoft.com/default...b;en-us;134913
XL: GetCustomListNum Returns Unexpected List Number

--
Regards,
Tom Ogilvy


"fugfug" <fugfug.1s4087_1121260247.0512@excelforum-nospam.com> wrote in
message news:fugfug.1s4087_1121260247.0512@excelforum-nospam.com...
>
> I've changed it to OrderCustom:=(Application.CustomListCount) + 1 and it
> seems to work, still a bit confused as to what customlistcount does.
>
> James
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=386709
>

[Bob Phillips]

James,

I have used custom lists in VBA a bit but never come across this. Your
workaround is confirmed by Tom, but it seems a bit flaky. Did you create the
custom list just prior to sorting, or was it already present?

--
HTH

Bob Phillips

"fugfug" <fugfug.1s4087_1121260247.0512@excelforum-nospam.com> wrote in
message news:fugfug.1s4087_1121260247.0512@excelforum-nospam.com...
>
> I've changed it to OrderCustom:=(Application.CustomListCount) + 1 and it
> seems to work, still a bit confused as to what customlistcount does.
>
> James
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=386709
>

[fugfug]

Yep, just prior to sorting. The code I posted is the final part of a macro which also includes the creation of the custom list. I tested the final bit of code without the rest of macro aswell but the same thing still happens. It works fine now with the +1 but it is an interesting qwerk!