Named Range Update Reference Using VBA

[mizogy]

Hi

Wondering whether anyone could give a point in the right direction to the correct code I should be using when updating a Named Range using VBA.

I have cell in my spreadsheet "A1" which provides the last row I need my name range to be extended to. I've then made the value of this cell a variant so the named range can then be dynamically updated each time my underlying data is refreshed.

Dim AB As Variant
AB = Worksheets("Sheet1").Range("A1").Value
ActiveWorkbook.Names("Model1").RefersToR1C1 = _
("=Sheet1'!$y$18:$y$" & AB)

Any help would be greatly appreciated!

Thanks Mizogy

 

[JoeMo]

Sub UpdateNamedRange()
Dim x As Long
x = Range("A1").Value
With ActiveWorkbook.Names("Model1")
    .RefersTo = Range("Model1")(1).Resize(x)
    MsgBox .RefersTo
    MsgBox Range("Model1").Parent.Name & "!" & Range("Model1").Address
End With

End Sub