Thread: Dynamic Cell referencing in range function for vba in excel

Hi All,
I am just presenting the vba code below for sorting all columns (A4:T379) based on a value in a single column (M)

Selection.AutoFilter Field:=rownumber, Criteria1:="<01-Feb-2011", Operator:=xlAnd
    Range("A4:T379").Sort Key1:=Range(M3), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

The above code sorts the range from A4 to T379 which is nothing but the data below the column headings which has autofilter in it.

My question:
Is it possible to substitute A4 and T379 with two variables which contains the address of the first cell and the last cell in the range. If so, Can anyone provide me with a syntax to substitute variables for A4 and T379 in the range function.

Adv. Thanks for the help.
KK

You could be missing part of the code, what is rownumber?
try this

    Dim LstRow As Long, Rng As Range, Strng As Range

    Set Strng = Range("A3")

    LstRow = Cells(Rows.Count, "A").End(xlUp).Row

    Set Rng = Range(Cells(3, 1), Cells(LstRow, 13))

    Strng.AutoFilter Field:=1, Criteria1:=">=2/4/2011", Operator:=xlAnd

    Rng.Sort Key1:=Range("M3"), Order1:=xlAscending, Header:= _
             xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
             DataOption1:=xlSortNormal

I am sure you can use rng in place of strng as well