Sort key range query

Question

Why does this work:

Range(Cells(1, 1), Cells(5, 5)).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

But this doesn't?:

Range(Cells(1, 1), Cells(5, 5)).Select
Selection.Sort Key1:=Range(Cells(1,1)), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

Saying the

Method Range failed

EDIT

The reason for asking is that I would like the sort key to be dynamic, such as:

Selection.Sort Key1:=Range(Cells(intRow, intCol))

I can't see how this is done.

Answer 1

The reason for asking is that I would like the sort key to be dynamic ...

At least part of the problem is relying on .Select and the subsequent Selection as the working area. If your intention is to work with the Range.CurrentRegion property (the 'island' of data origination in A1) then use a With ... End With statement to define the .CurrentRegion and work with it.

with worksheets("Sheet1")     `<~~ set the parent worksheet!
    with .Cells(1, 1).CURRRENTREGION         `<~~ set the 'island' of data
        .cells.Sort Key1:=.cells(1), Order1:=xlAscending, Header:=xlNo, _
                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal
    end with
end with

I'm a little unclear on what you mean by 'dynamic'. The above will use the cell in the top-left of the area defined by .CurrentRegion. If you used With .Range("D5:H99") then .Cells(1) would refer to D5.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Answer 2

It's always best to qualify exactly which objects you are working with and work directly with the objects, as opposed to using Selection or just Range, as it can sometimes lead to unintended consequences or slow your code down.

Try this:

Dim ws as Worksheet
Set ws = Sheets("Sheet1") ' replace with your sheet name

'assume you wrap this around a loop to move through intRow and intCol _
 or set them in some other fasion
With ws.Range(Cells(1,1), Cells(5,5)

  .Sort Key1:=.Cells(intRow, intCol), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

End With

Answer 3

The Cells call is already returning a Range object, so you should use

Selection.Sort Key1:=Cells(1,1), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

I think that your confusion is stemming from the fact that passing two Cells parameters to Range is valid, i.e. Range(Cells(1, 1), Cells(5, 5)), but it is not valid to only pass one Cells parameter, i.e. Range(Cells(1, 1))

You can see this for yourself with the following snippet

Public Sub test()

Dim rng As Range

Set rng = Range(Cells(1, 1), Cells(3, 1))
MsgBox rng.Cells.Count

Set rng = Range(Cells(1, 1))
MsgBox rng.Cells.Count

End Sub

You will get a message saying 3 for the first msgbox call, but you get an exception when trying to set rng the second time.

As to why the second format is not valid, I have no idea. If you find out why the devs built it this way, let us know.