How to sort rows on column A in ListObject VBA with parameter in Column B

Question

I've got a table in Excel which is filled through a Listobject macro from a database.

I am able to sort the table through following code:

'Sort on projecttabel
Dim lo As Excel.ListObject

Set lo = Plan.ListObjects(1)
With lo
.Sort.SortFields.Clear
    .Sort.SortFields.Add _
        Key:=Range("Table_appnlvd03_nkmgo_report_NKMGO_Report__KHNS_EFTTS_KPI32[klant]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Sort.SortFields.Add _
        Key:=Range("Table_appnlvd03_nkmgo_report_NKMGO_Report__KHNS_EFTTS_KPI32[Status]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Sort.SortFields.Add _
        Key:=Range("Table_appnlvd03_nkmgo_report_NKMGO_Report__KHNS_EFTTS_KPI32[RFO plandatum]"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    .Sort.SortFields.Add _
        Key:=Range("Table_appnlvd03_nkmgo_report_NKMGO_Report__KHNS_EFTTS_KPI32[Uitvoeren montage plan start]"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal

    With .Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

The "Status" has multiple integer values ranging from 1 to 100. What i would like to do is to is sort rows based on their status AND another column. The problem I encounter is that the "second column"to sort on is different for each status.

What i Would like to create is a "Gant" like table where the different date columns are sorted Descending for each status. For example (DD-MM-YYYY)

[Status]   [date1]              [date2]         [date3]
1         **10-01-2015**       12-02-2015        31-03-2015      
1         **09-01-2015**       14-02-2015        10-03-2015          
1         **08-01-2015**       06-02-2015        05-03-2015  
1         **07-01-2015**       18-02-2015        28-03-2015  
2         17-01-2015       **15-02-2015**        10-03-2015  
2         27-01-2015       **14-02-2015**        10-03-2015  
2         04-01-2015       **13-02-2015**        16-03-2015  
2         31-01-2015       **05-02-2015**        10-03-2015  
5         18-01-2015       12-02-2015        **30-03-2015**  
5         31-01-2015       18-02-2015        **27-03-2015**  
5         17-01-2015       04-02-2015        **26-03-2015**  
5         07-01-2015       27-02-2015        **08-03-2015**  
5         09-01-2015       24-02-2015        **03-03-2015**

etc. Is this possible?

If you already have the ListObject as a referenced object, then you really don't need to reference the ranges in this fashion. Just try this: with .lo.Sort .Fields.Add .ListColumns("kanlt").Range, xlSortOnValues, xlAscending, xlSortNormal. 'ADD OTHER SORT FIELDS HERE' .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With — William
Since this isn't an answer to the question, I have posted it in comments, but this would read better if the \n rendered in comments (or if there was a way to add a code-block in comments) — William

EEM EEM · Accepted Answer · 2015-09-28T12:55:25

Need to add a working field to the table, call it "Sort.Key" then enter this formula in that field:

=CONCATENATE([@Status],CHAR(133),CHOOSE([@Status],[@date1],[@date2],,,[@date3]))

Now sort the table using the "Sort.Key" field

How to sort rows on column A in ListObject VBA with parameter in Column B

1 Answers