如何使用 VBA 重新定义命名范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11031891/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I redefine a named range with VBA?
提问by mezamorphic
I have a named range called "X" which is 1000 rows and I want to dynamically reduce this to 100.
我有一个名为“X”的命名范围，它有 1000 行，我想动态地将其减少到 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?
我尝试过Range("X").Resize(100,1)，.Resize(-900,0)但当我通过从范围下拉菜单中选择范围来签入 excel 时，似乎都没有改变命名范围的大小。我究竟做错了什么？
回答by Jean-Fran?ois Corbett
Let's assume you have a named range called "myRange". If you do this:
假设您有一个名为“myRange”的命名范围。如果你这样做：
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 myRangeas its initial state. Then resize rto something else. What you've done is you've resized r, not myRange.
你的意思是：我有这个 range r。将其设置为匹配myRange作为其初始状态。然后调整大小r为其他内容。你所做的是你调整了大小r，而不是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:$C"
' or, resize relative to old reference:
With nr
.RefersTo = .RefersToRange.Resize(100, 1)
End With
回答by mezamorphic
Thank you for your contributions. Based on your help I was able to put together this code to resize a range and post n rows from an array to it.
感谢你的贡献。根据您的帮助，我能够将此代码放在一起以调整范围的大小并将数组中的 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
