Skip to content

Excel VBA – How to Sort within an Excel Table with VBA Macro

    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