vba 如何使用具有命名范围的偏移方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17410357/
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 to use the offset method with named ranges
提问by user2498217
There maybe better logic to this, but using the note to the user is the only way I understand making this selection work. Maybe you have something better.
这可能有更好的逻辑，但使用给用户的注释是我理解使此选择起作用的唯一方法。也许你有更好的东西。
I'm pretty much a newbie running Excel 2010 on an Windows XP machine.
我几乎是在 Windows XP 机器上运行 Excel 2010 的新手。
I have a named range (say A2:D8) that I call GanttArea. In A8 is a note to the user saying "Any additional lines must be inserted above this line." This is because I don't know how else to make sure the user adds their additional lines in the right place, and I don't know if they will add other lines a few cells later under here that don't apply to what I'm trying to capture.
我有一个命名范围（比如 A2:D8），我称之为 GanttArea。在 A8 中是给用户的注释，说“必须在此行上方插入任何其他行”。这是因为我不知道如何确保用户在正确的位置添加他们的附加行，而且我不知道他们是否会在后面的几个单元格中添加其他行，这些行不适用于我正在尝试捕捉。
If I enter
如果我输入
Sub SelectRange()
Range("GanttArea").select
End Sub
It will select the named range. I want it to move up one row so not to include my note. If I enter
它将选择命名范围。我希望它向上移动一行，以便不包括我的笔记。如果我输入
Sub SelectRange()
Range("GanttArea").Offset(-1,0).Select
End Sub
It does move the row up for the selection like I want but now it includes the row A1:D1 which are the heading rows. I'm trying to select the data dynamically for a gantt chart so this won't work.
它确实像我想要的那样将行向上移动以进行选择，但现在它包括作为标题行的 A1:D1 行。我正在尝试为甘特图动态选择数据，因此这不起作用。
It just occurred to me that maybe the offset is not changing the selection but just moving the same number of selected cells up one row.
我突然想到，偏移量可能不会改变选择，而只是将相同数量的选定单元格向上移动一行。
How can I make the named range expand or contract so the user can add or delete rows?
如何使命名范围扩展或收缩，以便用户可以添加或删除行？
Thank you, Kirk
谢谢你，柯克
回答by David Zemens
You can use the Resizemethod instead of Offset, so:
您可以使用该Resize方法代替Offset，因此：
Sub SelectRange()
Dim rng as Range: Set rng = Range("GanttArea")
Set rng = rng.Resize(rng.Rows.Count - 1, rng.Columns.Count)
rng.Select
End Sub
Alternatively, you could simply define your named range using the Offsetfunction.
或者，您可以简单地使用该Offset函数定义您的命名范围。
These are a little tricky to set up, but as long as your column A does not contain any blank cells, this should work. In your names manager, enter this formula for the GantArea:
这些设置有点棘手，但只要您的 A 列不包含任何空白单元格，这应该可以工作。在您的名称管理器中，为 GantArea 输入以下公式：
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,4)
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,4)
If you do this, you should be able to insert additional rows anywhere in the table area and as long as those rows are not empty values in column A, the range will resize dynamically based on that formula.
如果这样做，您应该能够在表格区域的任何位置插入额外的行，只要这些行在 A 列中不是空值，范围就会根据该公式动态调整大小。
