Referencing Ranges Regularly

[TwistShout]

This is a quality of life issue.....
Is there a better way to refer to a dynamic range than
Range(Range("A2"), Range("A2").end(xldown))
or
Range(Range("A2"), Range("B2").end(xldown)).

My code starts to packed and jumbled and ,if possible, this could make it easier to review/create.

potential thanks.

 

[sous2817]

Not tested but you could do something like:

dim rng as Range
set rng = Range(Range("A2"), Range("A2").end(xldown)) 

with rng
...
...
...
end with

 

[asking]

Hi TwistShout,
Try ~
Go to formulas menu, click name manager, click new, enter a name say Arg, in the refers to box copy and paste this formula into the refers to box ~

=OFFSET($A$1,1,0,COUNTA(OFFSET($A$1,1,0,9999)),1)

Now you can refer to the dynamic range as ~
dim rng as Range
set rng = Range("Arg")

with rng
...
...
...
end with

Cheers

 

[TwistShout]

thanks, both answers are basically what I was looking for. I will have to take a few minutes to break down asking's formula tho.