Custom sort without custom list

Hello everybody
I have a sheet named "Data" which contains cutom list on which I need to sort column C in sheets("Report")
I don't want to use custom list feature ( Application.AddCustomList )


I need to depend on the custom list in range("B2:B8") in sheets("Data")

I found UDF function but can't apply it
This my try but I got an error

Please Login or Register  to view this content.

The error is at this line

Please Login or Register  to view this content.

Sort method of range failed 1004

You must use Sort object not Sort method:

Please Login or Register  to view this content.

Sortitems function returns string with leading comma but this does not seem to matter.

Mr. Izandol
Thank you very very much for this correction
It works great

Another point in this thread I need to ask about .. what if I have a nother cutom list in another range and I need to apply it too
How can I do that?
See the attachment for second custom list

You may add another sort field in same way

Thanks for reply

I tried that and everything is ok

Please Login or Register  to view this content.

But is there an efficient way to make the function more flexible as I intend to add more than two custom lists ..?

That is not problem - you must only create new sort list and use with CustomOrder on new sortfield.

Last point I need add column E but sort it in normal way in ascending order
It is now OK with column C and D but not ok in column E

Originally Posted by Izandol

That is not problem - you must only create new sort list and use with CustomOrder on new sortfield.

I know but I am searching for a way to merge all in one function
instead of creating one by one

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

to be only in one

Please Login or Register  to view this content.

so as to have just one function for all custom lists

Mr. Nilem
I really apprciate this great and wonderful help
It is very very well after I removed this line and the results are ok for column C & D

Please Login or Register  to view this content.

After adding this line again
I got incorrect results ..
My problem now is with column E only ..but as for custom sort it is ok

Thanks a lot I solved last point by putting this line

Please Login or Register  to view this content.

After these two lines

Please Login or Register  to view this content.

I would suggest more general function:

Please Login or Register  to view this content.

Posted two times

Thank you very much Mr. Izandol
I got an error at this line

Please Login or Register  to view this content.

'Error 438 : Object doesn't support this property or method

Yes - I fix this just now. Please use changed code.

Great & Excellent & Awesome. Thank you very much Mr. Izandol
I'm very pleased with your sharing in this thread
Mr. Nilem
Thanks a lot for great and wonderful help you offered
Kind Regards