Define a dynamic named range, VBA

[dan_l]

I have need to create a 'live' dynamic named range using VBA. Non-VBA, I would just add a named range referring to:


=offset($a$a,0,0,count($a$a),1)


This would give me a range that expand and contracts automatically. I need this effect in place in VBA. I'm trying to do it with this line:


ActiveWorkbook.Names.Add Name:="test_range", RefersTo:=Range("offset($a$1,0,0,counta($A:$A),1)")


Trouble is, excel automatically calculates the result of the offset and the resulting range is fixed size (unless of course the sub runs again, but that's not practical for this experiment)


Thoughts?

 

[Luke M]

You don't want it to refer to a Range, just the formula:


ActiveWorkbook.Names.Add Name:="test_range", RefersTo:="=offset($a$1,0,0,counta($A:$A),1)"

 

[dan_l]

Ahhhh, the old missing equal sign thing.


I fail at failing.


Thanks.