Sorting with vba, custom sort?

[Lil2606]

Hi all,

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

[COLOR=#333333]
<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: https://www.excelforum.com/excel-programming-vba-macros/1264622-sorting-with-vba-custom-sort.html

 

[Fluff]

Why not do it the same way I showed here
https://www.mrexcel.com/forum/excel...-multiple-rows-based-multiple-conditions.html

 

[Lil2606]

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..

 

[Fluff]

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

 

[Lil2606]

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..

 

[Lil2606]

Actually.. it did.. Nevermind me!

 

[Fluff]

Do you have any other CF on table that includes that column?

 

[Lil2606]

Well...

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?

 

[Fluff]

Can you supply an updated file?
Also can you please answer my question in post#7?

 

[Lil2606]

Sorry I didn't refresh. No I made sure to remove all conditional formatting from the entire sheet before I ran the macro..

Do you have an account on this page? https://www.excelforum.com/excel-programming-vba-macros/1264622-sorting-with-vba-custom-sort.html easier to upload stuff there