Excel SortFields add then sort

问题:

Would you help me understand this snippset:

First, it seems that a sorting rule is added with

MainSheet.Sort.SortFields.Clear
For lI = 1 To vSortKeys(0, 1)   
    MainSheet.Sort.SortFields.Add Key:=Range(vSortKeys(lI, 1) & 2), 
       SortOn:=xlSortOnValues, Order:=vSortKeys(lI, 2), DataOption:=xlSortNormal
Next

Then, I understand that the following code is effectively running the sort

With MainSheet.Sort
    .SetRange Range("A" & lFrom & ":" & GEN_REV_END & lTo)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Is this interpretation correct - need to add a sorting rule first, then apply it with the second part ?

Then, why are we defining a range for sort in the second part, in

With MainSheet.Sort
    .SetRange Range("A" & lFrom & ":" & GEN_REV_END & lTo)

End With

Is it not already in the rule that we sort for Key:=Range(vSortKeys(lI, 1) & 2) ? On which range of cells is the sort effectively run ?

回答1:

The sorting is being applied to the range specified in Sort.SetRange. The Key parameter in Sort.SortFields.Add allows you to specify fields that will determine the order of soring. Each field can be just the cell with the column header. You can add multiple keys for several sorting levels.

To give an example, if you have data in cells A1:C10 and you want to sort it in an ascending manner, taking information in column A as the key for sorting, you can do this to set data in column A as the key:

MainSheet.Sort.SortFields.Add Key:=Range("A1") '("A1:A10") will also work

And then you can specify the range that will be sorted based on that key as follows:

 MainSheet.Sort.SetRange Range("A1:C10")