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)

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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