Dynamic Cell referencing in range function for vba in excel

**karunakaranindia** · 12-22-2010, 09:23 AM

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

**StephenR** · 12-22-2010, 09:33 AM

Do you mean like this?

s1 = "$A$4"
s2 = "$T$379"
Range(s1, s2).Select

Please can you use code tags as per forum rules.

**davesexcel** · 12-22-2010, 10:01 AM

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

**karunakaranindia** · 12-23-2010, 01:30 AM

@ StephenR Thanks for the reply. A4 and T379 references will change as I run several iterations. Hence I was looking for variables that will store the different cell address during each iteration. Sorry for not tagging the code.

@davesexcel. Thanks for the reply and code.

Row number is a variable that contains the field number of the column based on which the range is to be sorted. The suggested code worked well for me with variables inserted under cells function in range instead of numbers. I was looking for exactly the same thing.

Bye,
KK