Как переопределить именованный диапазон с помощью VBA?

У меня есть именованный диапазон под названием "X", который составляет 1000 строк, и я хочу динамически уменьшить это до 100.

Я пробовал Range("X").Resize(100,1), а также .Resize(-900,0), но не изменил размер именованного диапазона, когда я проверяю excel, выбирая диапазон из раскрывающегося меню диапазона. Что я делаю неправильно?

Ответы

Ответ 1

Предположим, что у вас есть именованный диапазон, называемый "myRange". Если вы это сделаете:

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

Что вы говорите: у меня этот диапазон r. Установите его в качестве начального состояния myRange. Затем измените размер r на другое. Вы сделали то, что вы изменили размер r, а не myRange.

Чтобы изменить размер именованного диапазона, вам нужно сделать что-то вроде этого:

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

Ответ 2

Спасибо за ваши вклады. Основываясь на вашей помощи, я смог собрать этот код, чтобы изменить размер диапазона и отправить n строк из массива в него.

Sub PostArrayLinesToRange(ByVal rngName As String, ByRef arr As Variant, ByVal r As Long)
    Dim wb As Workbook
    Dim rng As Range
    Dim nr As Name          'Variable to change named range size definition

    Set wb = ThisWorkbook
    Set rng = Range(rngName)

    rng.Clear
    Set nr = wb.Names.item(rngName)
    With nr
        .RefersTo = .RefersToRange.Resize(r, UBound(arr, 2))
    End With
    Set rng = Range(rngName)
    rng = arr
    Erase arr
    Set nr = Nothing
    Set rng = Nothing
    Set wb = Nothing
End Sub