How to reference variable rectangular range?

[Joe User]

I have the following variables:

Const cellName As String = "B2"
Const nRow As Long = 5
Const nCol As Long = 10

I want to reference the rectangular range starting from cellName through row
cellName+nRow and column cellName+nCol; that is, B2:K7 for the values above.

I've come up with the following:

Range(cellName, Range(cellName).Cells(nRow, nCol))

Seems tedious. Is that the best I can do?

 

[Rick Rothstein]

You would use the Resize property...

Range(cellName).Resize(nRow, nCol)

Note, however, that you counted your rows and columns differently... I get
Column K for nCol equal to 10; but, using the same counting method, I get
Row 6, not Row 7, for nRow = 5.

 

[Joe User]

Range(cellName).Resize(nRow, nCol)
Thanks.


I get Row 6, not Row 7, for nRow = 5.

My mistake. I was out sick the day they taught counting in kindergarten.



----- original message -----

 

[JLGWhiz]

Joe, you were not incorrect when you used the example row cellName + nRow.
That would be row 7. But when referenced as Range(cellName).Range(nRow,
nCol) the count for nRow becomes relative to cell B2, not in addition to it.

But Rick gave you the solution with the resize statement so the above is a
moot point.