Declare a Range relative to the Active Cell with VBA

Question

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  numRows and 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    Any suggestions on how to do this

User · Answer

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

User · Answer

There is an  Offset property on a Range class which allows you to do just what you need  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  and here s how to assign this range to an array

[vba] Declare a Range relative to the Active Cell with VBA

Examples related to vba

Examples related to excel