VBA to Custom Sort Data Based on Referenced List

Sort Example.xlsxHi All,

First time poster, but a long time beneficiary of this board's knowledge. I have a seemingly simple issue...

I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.

A8 = Customer
B8 = Load Time
C8 = Delivery Time

Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"

I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).

The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.

The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM

Example is attached (desired sort is reflected).

Any help is very appreciated!

Thanks.

Hi Jake,

I reattached my file with the data unsorted, as well as with it sorted in the desired order.

Does this help you understand my issue?

Best,
Tommy

I am getting a Run-time error 1004.

Debugging puts me at line

Application.AddCustomList Sheets("TimeSort").Range("Sort_Me").Value

To restate my question, I want my range to sort on Column B, but I want the sort to start at "PRELOAD", then go chronologically starting at 6:00 AM, 7:00 AM, etc

Jaslak,

(Misread that as Jake earlier).

Thanks! That completes the my desired sort, but it's crashing the file after running.

I think I've heard about this issue with excel 2010 or newer and creating custom lists via VBA, but not sure of the solution.


Thoughts?

I moved the .Sort.SortFields.Clear command to just before Application.DeleteCustomList... command.

That seems to have solved the problem and aligns with some other posts with this issue.

Thanks for the help!

One last thing...how do I mark this as solved?

Modified code below...

Please Login or Register  to view this content.