Okay so I've been struggling with an annoying problem.... I have some VBA code that needs to resize the range that a particular name points to. The only way I could see to do it is like this:
Code
[FONT="Courier New"][COLOR="navy"]Public Sub redefine_foo()
Dim r As Range
Set r = ActiveWorkbook.Names("NAMED_RANGE_FOO").RefersToRange
r = r.Resize(r.Rows.count + 1, r.columns.count + 1)
Call ActiveWorkbook.Names("NAMED_RANGE_FOO").Delete
Call ActiveWorkbook.Names.Add("NAMED_RANGE_FOO", r)
End Sub[/COLOR][/FONT]
But that has two problems... first, it purges all the data in the original range when I do the "Resize()" call. And Second, the resized range does not in fact get resized.
However, the delete/add on the named range does work.
So what I end up with is the original named range re-defined but with all of it's content removed.
How do I fix this so that the range resizes AND the data in the original range is preserved WITHOUT going through a long complicated process of stitching together a new range reference string?