使用 VBA 声明相对于活动单元格的范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25409175/
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
Declare a Range relative to the Active Cell with VBA
提问by BlueBerry
I need to declare a range object relative to the Active Cell. The problem is, the number of rows and columns I want to select is different each time the macro runs.
我需要声明一个相对于活动单元格的范围对象。问题是，每次运行宏时，我要选择的行数和列数都不同。
For example, I have two variables: numRowsand numCols.
例如，我有两个变量：numRows和numCols。
I want to select a range that has the ActiveCell in the upper-left corner hand has the cell with row ActiveCell.Row + NumRows and column ActiveCell.Column + NumCols in the bottom right (and then I intend to copy this data to an array to speed up my macro).
我想选择一个在左上角有 ActiveCell 的范围，在右下角有行 ActiveCell.Row + NumRows 和列 ActiveCell.Column + NumCols 的单元格（然后我打算将此数据复制到数组加速我的宏）。
Any suggestions on how to do this?
关于如何做到这一点的任何建议？
回答by
There is an .Offsetpropertyon a Range class which allows you to do just what you need
Range 类上有一个.Offset属性，它允许您执行所需的操作
ActiveCell.Offset(numRows, numCols)
ActiveCell.Offset(numRows, numCols)
follow up on a comment:
跟进评论：
Dim newRange as Range
Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols))
and you can verify by MsgBox newRange.Address
您可以通过以下方式验证 MsgBox newRange.Address
回答by Jean-Fran?ois Corbett
Like this:
像这样：
Dim rng as Range
Set rng = ActiveCell.Resize(numRows, numCols)
then read the contents of that range to an array:
然后将该范围的内容读入数组：
Dim arr As Variant
arr = rng.Value
'arr is now a two-dimensional array of size (numRows, numCols)
or, select the range (I don't think that's what you really want, but you ask for this in the question).
或者，选择范围（我认为这不是您真正想要的，但您在问题中提出了这一要求）。
rng.Select
