Sort by Value
This procedure sorts data in descending order of TRANS_VALUE in the data shown below.
Dim ws As Worksheet Set ws = ActiveSheet Dim tbl As ListObject Set tbl = ws.ListObjects("Sales_Table") Dim sortcolumn As Range Set sortcolumn = Range("Sales_Table[TRANS_VALUE]") With tbl.Sort .SortFields.Clear .SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending .Header = xlYes .Apply End With
The code above relates to the data shown below.
Sort by Cell Colour
To sort by cell colour, specify this in the SortOn parameter of the Add method. You then have to specify the colour to sort by using the SortOnValue property of the SortField object (see below).
Dim ws As Worksheet Set ws = ActiveSheet Dim tbl As ListObject Set tbl = ws.ListObjects("Sales_Table") Dim sortcolumn As Range Set sortcolumn = Range("Sales_Table[TRANS_VALUE]") With tbl.Sort .SortFields.Clear .SortFields.Add(Key:=sortcolumn, Order:=xlAscending, _ SortOn:=xlSortOnCellColor).SortOnValue.Color =RGB(255, 255, 0) .Header = xlYes .Apply End With
Sort by Icon
To sort by icon, specify this in the SortOn parameterof the Add method. You then have to specify the icon to sort by usingthe Set Icon method of the SortField object.
Dim ws AsWorksheet Set ws =ActiveSheet Dim tbl AsListObject Set tbl =ws.ListObjects("Sales_Table") Dim sortcolumnAs Range Set sortcolumn= Range("Sales_Table[TRANS_VALUE]") With tbl.Sort .SortFields.Clear .SortFields.Add(Key:=sortcolumn, Order:=xlAscending, _ SortOn:=xlSortOnIcon).SetIcon _ Icon:=ActiveWorkbook.IconSets(xl3TrafficLights1).Item(1) .Header =xlYes .Apply End With