How do I redefine a named range with VBA?

Question

I have a named range called "X" which is 1000 rows and I want to dynamically reduce this to 100.

I tried Range("X").Resize(100,1) and also .Resize(-900,0) but neither seem to change the size of the named range when I check in excel by selecting the range from the range drop-down menu. What am I doing wrong?

Answer 1

Let's assume you have a named range called "myRange". If you do this:

Dim r As Range
Set r = Range("myRange")
Debug.Print r.Resize(10, 1).Address

What you are saying is: I have this range r. Set it to match myRange as its initial state. Then resize r to something else. What you've done is you've resized r, not myRange.

To resize a named range, you need to do something like this:

Dim wb As Workbook
Dim nr As Name

Set wb = ActiveWorkbook
Set nr = wb.Names.Item("myRange")

' give an absolute reference:
nr.RefersTo = "=Sheet1!$C$1:$C$9"

' or, resize relative to old reference:
With nr
    .RefersTo = .RefersToRange.Resize(100, 1)
End With