Declare range relative to active cell with VBA

Question

I need to declare a range object relative to the active cell. The problem is that the number of rows and columns I want to select is different every time I run the macro.

For example, I have two variables: numRows and numCols .

I want to select a range in which ActiveCell in the upper left corner has a cell with the row ActiveCell.Row + NumRows and the column ActiveCell.Column + NumCols in the lower right corner (and then I intend to copy this data to the array to speed up my macro).

Any suggestions on how to do this?

Answer 1

The Range class has an .Offset property that allows you to do exactly what you need.

ActiveCell.Offset(numRows, numCols)

follow the comment:

 Dim newRange as Range Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols)) 

and you can check MsgBox newRange.Address

and here how to assign this range to an array

Answer 2

Like this:

 Dim rng as Range Set rng = ActiveCell.Resize(numRows, numCols) 

then read the contents of this range to the array:

 Dim arr As Variant arr = rng.Value 'arr is now a two-dimensional array of size (numRows, numCols) 

or, select a range (I don’t think you really want it, but you ask for it in the question).

 rng.Select