VBA table sort: Sortfields.Add Key:= syntax problem

Hans Troost

New Member
Joined
Jan 6, 2015
Messages
30
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Dear all

Background and context of my question:

Have project using Power Query to get data followed by VBA-code that copies the resulting table to new workbook and format it there: conditional formats, etc.
Works fine and showed a result to user who had only 1 comment: the sorting order: PQ sorts diacritic characters like Ö after the Z, while the excel sort that they are used to places e.g. names like "Öt***"
in the O-range, between "Ob***"and "Oz***": "Ob***","Öt***","Oz***"

So I decided to move the sorting from PQ to VBA-code but had no idea about syntax.

Luckily I had a result file from the PQ/VBA project and used that to get example code using the Macro Recorder and the Data -- Sort dialog, to sort on 2 columns.
Resulting code:

VBA Code:
    ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst").Sort. _
        SortFields.Add Key:=Range("Uitgiftelijst[Naam]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst").Sort. _
        SortFields.Add Key:=Range("Uitgiftelijst[Straat]"), SortOn:=xlSortOnValues _
        , Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("23-03 totaal").ListObjects("Uitgiftelijst"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

So using this as an example it seemed easy:

VBA Code:
'
' now the formatting of the target table
'
Set tTbl = tSh.ListObjects("Uitgiftelijst")

' Opmaak van de hele tabel: sortering, font, Borders etc.
'
With tTbl
'
'   Sortering in VBA, want Power Query sort plaatst diacritische tekens na de Z,
'   zodat bijv. ÖZTÜRK onderaan de lijst komt. De gebruiker van de lijst verwacht deze
'   naam bij de O
'
     With .Sort
        .SortFields.Clear
'
'  Based on the code from recorded macro. result: error 1004
'        .SortFields.Add Key:=Range("Uitgiftelijst[Naam]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=Range("Uitgiftelijst[Straat]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' from https://www.reddit.com/r/vba/comments/30jmu8/sorting_a_tablelistobject_by_customorder_driving/
' result error 1004
'        .SortFields.Add Key:=.Parent.ListColumns("Naam").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=.Parent.ListColumns("Straat").Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' inspired by the above
'result: error 1004
'        .SortFields.Add Key:=.Parent.ListColumns("Naam").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=.Parent.ListColumns("Straat").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'some others:
'        .SortFields.Add Key:=tTbl.ListColumns("Naam").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=tTbl.ListColumns("Straat").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=tTbl.ListColumns("Naam"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=tTbl.ListColumns("Straat"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
' could find back where I got this from.
'        .SortFields.Add Key:=Range("Uitgiftelijst[[#All],[Naam]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=Range("Uitgiftelijst[[#All],[Straat]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'  from https://stackoverflow.com/questions/46738840/how-to-refer-to-this-table-column-by-name-when-sorting and
'  https://excel.officetuts.net/en/vba/sort-columns-in-table
' result: error 5.
'        .SortFields.Add Key:=.Parent.Range("Uitgiftelijst[Naam]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal
'        .SortFields.Add Key:=.Parent.Range("Uitgiftelijst[Straat]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlNormal

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

I seems that I do not understand the use of table columns here and do something stupid.

Remark: the statement "Set tTbl = tSh.ListObjects("Uitgiftelijst")" works and is used by the - not shown - code below and has to be kept.

Any help regarding the correct syntax of the Sortfields.add Key:=??? will be very appreciated.

Kind regards, Hans
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
VBA Code:
    Dim sh As Worksheet
    Dim tTbl As ListObject
    Dim lr As Long
    Dim ic As Integer
    Set sh = ThisWorkbook.Sheets("Table")
    Set tTbl = sh.ListObjects("Uitgiftelijst")
    With tTbl.sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("Uitgiftelijst[[#All],[Naam]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=Range("Uitgiftelijst[[#All],[Straat]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
Upvote 0
VBA Code:
    Dim sh As Worksheet
    Dim tTbl As ListObject
    Dim lr As Long
    Dim ic As Integer
    Set sh = ThisWorkbook.Sheets("Table")
    Set tTbl = sh.ListObjects("Uitgiftelijst")
    With tTbl.sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("Uitgiftelijst[[#All],[Naam]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=Range("Uitgiftelijst[[#All],[Straat]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Thanks, for your fast response.
and as soon as I read it I tried, since I was not ware of the add2-method (just the .add one).

Unfortunately it still raises the 1004 error.

Perhaps - if you want, I can provide you with a sample sheet - just prepared - and the full code? (just 435 lines including english/dutch comments - a bit inconsistent). The Sort lines are 92 and 93.

Before I post the code here, I give you the mini-sheets with example data and wait I you are willing to test it will the full code. If not - no problem - I just ask for the case of and will not bother you.

This is the resulting sheet from the Power Query:

Uitgiftelijst.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1VB.nuW/D/VL.U.VKTKWSpeciaalPakketAanh.NaamStraatAanwBez.LeegPCWoonplaatsNiet geweestAfgemelddatumstopzettingredenstopzettingOpmerkingJongstePas
257624-06-21224familieKarelse13-01;30-0104-0224-06-218576
3536W25-04-21123halalde heerBakker11-11;18-11;25-11;09-12;01-01Meerdere malen niet geweest0536
4578V28-02-2111Groen [GD]mevrouwBoer de578
5535W26-05-21246halalfamiliePietersen26-05-210
6559M26-07-2111de heerPietersen26-07-21
7565W09-03-2111KEIde heertest 209-03-21
8575V04-07-2111Blauw [GD]halalde heerBijlsma
9577W30-04-2111mevrouwJansen
Uigiftelijst


and this is the result of the vba-code - without the sorting of cause, since that doesn't work yet.

2021-03-24.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1VB.nuW/D/VL.U.VKTKWSpeciaalPakketAanh.NaamStraatAanwBez.LeegPCWoonplaatsNiet geweestAfgemelddatumstopzettingredenstopzettingOpmerking
257624-6-2021224familieKarelse13-01;30-0104-0224-6-2021
3536W25-4-2021123halalde heerBakker11-11;18-11;25-11;09-12;01-01Meerdere malen niet geweest
4578V28-2-202111Groen [GD]mevrouwBoer de
5535W26-5-2021246halalfamiliePietersen26-5-2021
6559M26-7-202111de heerPietersen26-7-2021
7565W9-3-202111KEIde heertest 29-3-2021
8575V4-7-202111Blauw [GD]halalde heerBijlsma
9577W30-4-202111mevrouwJansen
24-03 totaal


In case you want the full code, play around please let me know, I will post it here.

BTW: no difference between XL 2016 and XL 2019 regarding to this.

Best regards, Hans
 
Upvote 0
Thanks, for your fast response.
and as soon as I read it I tried, since I was not ware of the add2-method (just the .add one).

Unfortunately it still raises the 1004 error.

Perhaps - if you want, I can provide you with a sample sheet - just prepared - and the full code? (just 435 lines including english/dutch comments - a bit inconsistent). The Sort lines are 92 and 93.

Before I post the code here, I give you the mini-sheets with example data and wait I you are willing to test it will the full code. If not - no problem - I just ask for the case of and will not bother you.

This is the resulting sheet from the Power Query:

Uitgiftelijst.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1VB.nuW/D/VL.U.VKTKWSpeciaalPakketAanh.NaamStraatAanwBez.LeegPCWoonplaatsNiet geweestAfgemelddatumstopzettingredenstopzettingOpmerkingJongstePas
257624-06-21224familieKarelse13-01;30-0104-0224-06-218576
3536W25-04-21123halalde heerBakker11-11;18-11;25-11;09-12;01-01Meerdere malen niet geweest0536
4578V28-02-2111Groen [GD]mevrouwBoer de578
5535W26-05-21246halalfamiliePietersen26-05-210
6559M26-07-2111de heerPietersen26-07-21
7565W09-03-2111KEIde heertest 209-03-21
8575V04-07-2111Blauw [GD]halalde heerBijlsma
9577W30-04-2111mevrouwJansen
Uigiftelijst


and this is the result of the vba-code - without the sorting of cause, since that doesn't work yet.

2021-03-24.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1VB.nuW/D/VL.U.VKTKWSpeciaalPakketAanh.NaamStraatAanwBez.LeegPCWoonplaatsNiet geweestAfgemelddatumstopzettingredenstopzettingOpmerking
257624-6-2021224familieKarelse13-01;30-0104-0224-6-2021
3536W25-4-2021123halalde heerBakker11-11;18-11;25-11;09-12;01-01Meerdere malen niet geweest
4578V28-2-202111Groen [GD]mevrouwBoer de
5535W26-5-2021246halalfamiliePietersen26-5-2021
6559M26-7-202111de heerPietersen26-7-2021
7565W9-3-202111KEIde heertest 29-3-2021
8575V4-7-202111Blauw [GD]halalde heerBijlsma
9577W30-4-202111mevrouwJansen
24-03 totaal


In case you want the full code, play around please let me know, I will post it here.

BTW: no difference between XL 2016 and XL 2019 regarding to this.

Best regards, Hans

Apologies, I see now that the sample data I prepared are based on a database without Straat (=Street) filled: but will not effect anything: I have the same syntax problem...
 
Upvote 0
Have you changed also: DataOption:=xlSortNormal
 
Upvote 0
Solution
Have you changed also: DataOption:=xlSortNormal
No, I did not! By biggest fear became reality again: Before posting reading the code several times to avoid stupid typo's.
And now had had one which I all the time overloooked. Background: I always type in lowercase and as XL changes it, I consider it as valid. Did it now again... Stupid me.

Changed it to xlSortNormal and everything words fine now.

Thanks a lot for your support and patience.

Hans
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top