VBA Range.Sort...How do you select Key Range?

[PP3321]

Dear Chandoo Community,
Thank you always for your help...
Today, I have 1 question for sorting in VBA.

When you determine key1,
why do we take the first row in the column?

Sorry I am out of office today and cannot test it myself, but
what if I take last value in that column?
Or what if I select multiple cells?

On the worksheet, I often select the entire rows before sorting.
So it makes more sense to do it like this...

*No headers

Sub Sort()
Range("A1:B5").Sort Key1:=Range("B1:B5")
End Sub

Or

Sub Sort()
Range("A1:B5").Sort Key1:=Range("B")
End Sub

Instead of...

Sub Sort()
Range("A1:B5").Sort Key1:=Range("B1")
End Sub

Is it to do with type of sorting?
We take the first value because algorithm takes the first value and start comparing from there?

I saw this but was not quite clear...
Developer Reference for Range.Sort
https://msdn.microsoft.com/en-us/library/office/ff840646.aspx

Thank you...

 

[Chihiro]

Basically it's to specify which column to sort on.

Think of Key1, Order1 as 1st Sort by in Sort tool. Key2 as 2nd Sort (Then by) so on and so forth.

Key can be any one of following.

Cell
Range
Column Index.

It does not need to be the first cell. Sort logic will depend on parameters you specify and on range specified for sort.

If you use Columns("A:B"), sort will be done on entire columns.

If you have data with header (but not table). If you sort without specifying Header yes/no in above example, it can potentially sort header along with data (depending on data structure it may automatically consider row1 as header).

Generally speaking, it's safer to specify header, or specify sort range excluding header row.

 

[PP3321]

@Chihiro
Thank you very much for your answer.

I did testing and here is my update.

I have data in Column A & B.
A1 and B1 are headers.

When I did below, it sorted by Column A.

*********************************************
Sub Test()

Range("A1:B10").Sort

Key1:=Range("A:B"),
Header:=xlYes,
Order1:=xlAscending

End Sub
*********************************************

*These were all OK
(sort by Column B)

Key1:=Columns("B")
Key1:=Columns(2)

Key1:=Range("B10")
Key1:=Range("B:B")
Key1:=Range("B1")

 

[Chihiro]

That would expected. Since Key takes 1 column as argument only.

Therefore Range("A:B") will take first available column as argument.