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]

I do yes, but there is no file on that post.

As you have no other CF I would recommend using something like

With Range("table1[Basket size]")
   .FormatConditions.Delete
   .FormatConditions.Add Type:=xlTextString, String:="05", TextOperator:=xlBeginsWith
   .FormatConditions(.FormatConditions.Count).SetFirstPriority
   .FormatConditions(1).Interior.color = RGB(255, 255, 0)
End With

Otherwise every time you you run the code, you'll add the same CF rule time after time.

 

[Lil2606]

Uploaded the example file now.

Once the sort is completed I will remove the conditional formatting, so its not a problem if every time it will be re-added, but it is also part of a bigger code that fixes up that software generated sheet. Ideally the user will only have to press a button and will have the software generated sheet in a state, that they can produce reports and sorts from. Its like a converter/code

 

[Fluff]

How about

Sub Sort_Table()

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

   With Worksheets("Sheet1").ListObjects("table1").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("table1[Family name]"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortNormal
      .SortFields.Add Key:=Range("table1[First name]"), SortOn:=xlSortOnValues, Order:= _
            xlAscending, DataOption:=xlSortNormal
      .SortFields.Add(Range("table1[Basket size]"), xlSortOnCellColor, xlAscending, , xlSortNormal). _
            SortOnValue.Color = RGB(255, 255, 0)
      
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With

End Sub

 

[Lil2606]

Okay.. I rewrote the one in the sheet to be with .sort.sortfields.add and added the .apply line, and now it works.. but while with the .sort.add version the major sort had to be the last, with this one its the opposite way around.. a bit messy.

      .SortFields.Add(Range("table1[Basket size]"), xlSortOnCellColor, xlAscending, , xlSortNormal). _
            SortOnValue.Color = RGB(255, 255, 0)

If you write the code like this, does Intellisense still help out? (Or is there a keyboard shortcut for it? Or can it be turned on constantly?)

Thank you for brainstorming with me and all your help!

 

[Fluff]

Intellisense should work, as long as you use the sheet Codename, rather than Worksheets("...")

 

[Lil2606]

Yeah sometimes it works sometimes it doesn't sometimes it stops working halfway through.. nevermind I found the shortcuts Thank you!