How to define the Key in the sort method of a range

hello,
What is the best definition of the key in: ActiveSheet.Sort.SortFields.Add Key:= …..
if all the assigned keys in the attached workbook give the same result?
How should i interprete the key in this regard?
And what is the use of .SortFields.Clear?
It seems to make no difference at all.
After all, the sort method is applied on a given (current) state of a range.
Sorted or unsorted.

regards,
Stranno

If you sort your data by col A and don't use Sortfields.Clear, your sort will still use col A first.

Whatever range you choose for the key (and it makes sense to choose the column based on your data set size) you should qualify it properly with the relevant worksheet or you will get errors when you reopen the file (as with your posted sample).

Quote:

---

Originally Posted by Aflatoon

If you sort your data by col A and don't use Sortfields.Clear, your sort will still use col A first.

Whatever range you choose for the key (and it makes sense to choose the column based on your data set size) you should qualify it properly with the relevant worksheet or you will get errors when you reopen the file (as with your posted sample).

---

Sorry, what was wrong with my posted workbook? I reopened it and it produced an error indeed. But i did not understand what was wrong with it.
But you say the choice of the key does makes sense. What would you choose in this case?
Key:=Range("B6:B19") and Key:=Range("C6:C19") because of SetRange Range("A6:C19")?
I still don't get it. Whatever i choose, the result remains the same. The Sort seems to be executed in a right way.

The hereby attached workbook opens errorless (i still don't know what was wrong with the first one).

You should still be specifying the sheet for all your ranges:

Code:

---

Sub KOLBOPLO_1()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ActiveSheet.Range("B6:B19"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=ActiveSheet.Range("C6:C19"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange ActiveSheet.Range("A6:C19")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

---

' Range.sort : Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

Code:

---

Sub KOLBOPLO_1()
  With Blad1
      .Cells(1).CurrentRegion.Offset(5).Sort .Cells(6, 2), , .Cells(6, 3), , 2
    End With
End Sub


Sub Herstel()
  with Blad1
    .Range("L1:N25").Offset(, -11) = .Range("L1:N25").Value
  end with
End Sub

---

Quote:

---

Originally Posted by Aflatoon

You should still be specifying the sheet for all your ranges:

Code:

---

Sub KOLBOPLO_1()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ActiveSheet.Range("B6:B19"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=ActiveSheet.Range("C6:C19"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange ActiveSheet.Range("A6:C19")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

---

---

With specifying the sheet you mean replace WorkSheets(ActiveSheet.Name) by ActiveSheet? You're probably right. I copied this code
somewhere on the internet. Personally i would never use WorkSheets(ActiveSheet.Name).Sort.

But (with respect :-)) it’s not an answer to my question. I still wonder why all these different formulated keys do the same thing.
Is there a best way to specify a key in a sort routine? It looks to me that it doesn't matter at all.
On the other hand i can’t imagine that this would be the case.

No, I mean you should specify the sheet for the ranges you use as the Key and SetRange arguments, which you weren't doing before.

I suspect Excel is smart enough to simply base the sort on the column(s) you choose for the keys, but I can't see any benefit to not specifying the same rows for each column.

Since the key is only meant to indicate the column that should be sorted, it doesn't matter how you indicate that key:
.parent.Range("B1")
.parent.Range("B6")
.parent.Range("B1:B100")
.parent.Range("B:B")
.parent.columns(2)

Code:

---

Sub M_snb()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add .Parent.Columns(2)
        .SortFields.Add .Parent.Columns(3), Order:=xlDescending
        .SetRange Range("A6:C19")
        .Apply
    End With
End Sub

---

The clumsy original code you retrieved from the help file in the VBEditor.

Thanks both.
That was the answer i was looking for.
So in fact one may choose any cell (or range of cells in the same column) to specify the column that one wants to sort.