How to use VBA to do a sort with a variable for number of rows?

I am trying to use the following code to do a sort on a variable number of rows. The macro stops at the .Apply line. I have declared the "lastRow" variable and it has a value in it.

    Range(Cells(1, 1), Cells(lastRow, 6)).Select
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range(Cells(1, 1), Cells(lastRow, 6)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range(Cells(1, 1), Cells(lastRow, 6))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
EdLBAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
You haven't properly qualified all your Range and Cells calls with a Worksheet object. Try this:

    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Master")
    With ws.Sort
        With .SortFields
            .Clear
            .Add Key:=ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 6)), _
                        SortOn:=xlSortOnValues, Order:=xlAscending, _
                        DataOption:=xlSortTextAsNumbers
        End With
        .SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, 6))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Open in new window


or even:
    Dim ws                    As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Master")
    With ws
        .Range(.Cells(1, 1), .Cells(lastRow, 6)).Sort key1:=.Cells(1, 1), Order1:=xlAscending, _
                            Header:=xlYes, MatchCase:=False, DataOption1:=xlSortTextAsNumbers
    End With

Open in new window

EdLBAuthor Commented:
Neither worked. The first proposed solution stopped at .Apply.  The second proposed solution ran but nothing was sorted.  Sorry but I neglected to mention in my original post that I had declared the Worksheet object when I ran the original code. The full (relevant) code for my original was as is listed below. Since I recorded a Macro and just copied the code, it's not very clean but I don't think there are any conflicts in the object or variable definitions. As mentioned before, the variable lastRow was also declared and has a value.

Dim lastRow As Long
Dim MasterWS As Worksheet

Set MasterWS = Sheets("Master")    

MasterWS.Select

lastRow = MasterWS.Cells(Cells.Rows.Count, "A").End(xlUp).row

    Range(Cells(1, 1), Cells(lastRow, 6)).Select
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range(Cells(1, 1), Cells(lastRow, 6)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range(Cells(1, 1), Cells(lastRow, 6))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Martin LissOlder than dirtCommented:
Just change the Key to Key:=Range("A1") and it will work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rory ArchibaldCommented:
Ahh, I missed that your key was multiple columns - which one are you actually trying to sort on?
EdLBAuthor Commented:
Thanks guys. I was trying to sort on F1, so when I change the key to that it worked fine.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.