Sorting with vba, custom sort?


Board Regular
Jan 29, 2019
Hi all,

I am trying to sort a table in excel, I have the following code so far:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub Sort_Table()

Sheet6.ListObjects("Sort_table").Range.Sort Key1:=Sheet6.ListObjects("Sort_table").ListColumns("Basket size").Range, Order1:=xlAscending, Header:=xlYes

Sheet6.ListObjects("Sort_table").Range.Sort Key1:=Sheet6.ListObjects("Sort_table").ListColumns("Family name").Range, Order1:=xlAscending, Header:=xlYes

Sheet6.ListObjects("Sort_table").Range.Sort Key1:=Sheet6.ListObjects("Sort_table").ListColumns("First name").Range, Order1:=xlAscending, Header:=xlYes

End Sub</code>[/COLOR]
I know it can be done by key1, key2 and key3 but I did it this way in case I have to add more columns to sort by. I know it would be simple to do without vba, but would like to learn to do it this way.

So this code works all fine for now, but I would like to add a custom sorting order for the basket size sort. I have lots of basket sizes, I don't know the name of them, but I have ones that begin with 05 and I would like those ones to be the first ones, for each first name and family name combo.

I couldn't get it to work in normal excel sort either with the "05*" so I'm not sure how to do this..

I was also thinking the basket size column could be formatted so the cells in it beginning with 05 have a background colour, then do a colour sort? Not sure whats the best way to do this..

(I have done it with the macro recorder but that code seems like an overkill and I am bad at reverse engineering them...)

Please help?

Link to another site where I asked the same question:
Last edited:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Because I would like to keep the names in order as well, and because if I just write "05" and not EXACTLY whats in the cell it doesn't sort it right.. my real data set is a generated excel table from a software and users input stuff into this software sometimes a bit wrong.. so I'm trying to make this "foolproof" if they write 05 BAsket or 05basket or 05BASKET or 05bakset or 05anything it would still sort.. the one thing that is certain is that it begins with 05...

Sorry if I'm doing this forum thing wrong but I'm trying to ask 1 question at a time, I mean I could upload a dummy sheet and say I want this and this and this done, and someone would be awesome and would do the whole project for me, but I wouldn't exactly learn from that..
Last edited:
Upvote 0
OK, fair enough.
Two options that I can think of are
1) As you mentioned use Cf to format the cells with 05* & sort on colour
2) Add another column (which can be hidden) with a formula such as =LEFT([@[Basket size]],2)="05" and then sort on that column
Upvote 0
Thank you for understanding!

Sub conditional_formatting()

Range("Conditional_table[Basket size]").FormatConditions.Add Type:=xlTextString, String:="05", TextOperator:=xlBeginsWith
Range("Conditional_table[Basket size]").FormatConditions(Range("Conditional_table[Basket size]").FormatConditions.Count).SetFirstPriority
Range("Conditional_table[Basket size]").FormatConditions(1).Interior.Color = RGB(255, 255, 0)

End Sub

This is my conditional formatting code.. but I don't really understand the second line (reverse engineered from macro record) this SetFirstPriority business? What is that for?

I know the code didn't work without it..
Last edited:
Upvote 0
Do you have any other CF on table that includes that column?
Upvote 0

Sub Sort_Table()

Sheet9.Range("CS_table[Basket size]").FormatConditions.Add Type:=xlTextString, String:="05", TextOperator:=xlBeginsWith
Sheet9.Range("CS_table[Basket size]").FormatConditions(1).Interior.Color = RGB(255, 255, 0)

Sheet9.ListObjects("CS_table").Sort.SortFields.Add Key:=Sheet9.ListObjects("CS_table").ListColumns("Basket size").Range, SortOn:=xlSortOnCellColor, Order:=xlAscending

Sheet9.ListObjects("CS_table").Range.Sort Key1:=Sheet9.ListObjects("CS_table").ListColumns("Family name").Range, Order1:=xlAscending, Header:=xlYes

Sheet9.ListObjects("CS_table").Range.Sort Key1:=Sheet9.ListObjects("CS_table").ListColumns("First name").Range, Order1:=xlAscending, Header:=xlYes

End Sub

This is nearly working.. I mean the code works, and it formats the correct cells.. but the sort is not perfect.. for some reason,

John Page Apple Small basket and 05 basket, gets sorted correctly (05 at the very bottom of the Jon Page Apple range) but the John Smith Cherry Small basket and 05 basket is the other way around.. I can't think why?
Upvote 0
Can you supply an updated file?
Also can you please answer my question in post#7?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back