Set range and offset

[sammar12]

I am having trouble with one line of this code that I can't figure out how to write correctly. I have set the range, but I need to offset by 10 rows up. My code fails at set rng. Here is what I have so far

Dim LsTRow As Long
Dim rng As range
LsTRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = range("A7:q" & LsTRow).Offset(-10, 0).Select

With rng
    .Sort Key1:=.Cells(7, 3), Order1:=xlAscending, _
    Key2:=.Cells(7, 1), Order3:=xlAscending, Header _
    :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
    , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
    xlSortNormal
End With
End Sub

[shg]

If the range starts in A7, you can't offset it 10 rows up.

Please change your code tags from HTML to CODE.

[sammar12]

Could you suggest a method to get around this?

[shg]

Probably, if I knew what you were trying to do. Post a workbook and explain in context.

[sammar12]

Attached is a sample workbook of a similiar situation. This is a list of available products. Monthly the number of products can increase or decrease. I like to generate a sell sheet by item # for our sales people and alphabetical. Therefore I would like to set the range (in this example only through column c, but my actual book is through column Q). Once the range is set, I would like to sort based on Column c, however I would like to leave the last 10 items always at the bottom of the workbook.

[shg]

Ah, the clarity of an example.

Sub x()
    Dim lRow    As Long
    Dim rng     As Range

    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A7", Cells(lRow, "Q"))
    If rng.Rows.Count <= 11 Then Exit Sub
    Set rng = rng.Resize(rng.Rows.Count - 10)

    With rng
        .Sort Key1:=.Cells(1, "C"), Order1:=xlAscending, _
              Key2:=.Cells(1, "A"), Order1:=xlAscending, _
              Header:=xlNo, OrderCustom:=1, _
              MatchCase:=False, Orientation:=xlTopToBottom
    End With
End Sub