sorting in 2007

[shadow12345]

Hi,

Im sure this is simple but I can't work it out. All i am try to do is record my sort in VBA. I am selecting with ctrl + * and applying the sort, but the range can change so I tried to edit it.

This is what i got from the recorder

ActiveWorkbook.Worksheets("Source data").sort.SortFields.Add Key:=Range("B2:B1500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=    xlSortTextAsNumbers

I changed it to this (range selection first to last row i think),

lst = Range("A" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.Worksheets("Source data").sort.SortFields.Add Key:=Range("$B$2:$B$" & 1st), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=    xlSortTextAsNumbers

But its not working.

How can i make the first code look at all used cells?




**** If it is needed this is the whole code *****

  Cells.Select
    ActiveWorkbook.Worksheets("Source data").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Source data").sort.SortFields.Add Key:=Range( _
        "B2:B5123"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("Source data").sort.SortFields.Add Key:=Range( _
        "W2:W5123"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Source data").sort.SortFields.Add Key:=Range( _
        "A2:A5123"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Source data").sort
        .SetRange Range("A1:AF5123")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("E2").Select

 

[Jonmo1]

I don't think 1st is a valid variable name,

It works for me if I change 1st to LR

 

[shadow12345]

D'oh

thats working now.