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?

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.

--
Rick (MVP - Excel)


"Joe User" <joeu2004 wrote in message
...
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" wrote:
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 -----

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Joe User" <joeu2004 wrote in message
...
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?

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.


"Joe User" <joeu2004 wrote in message
...
"Rick Rothstein" wrote:
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 -----

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Joe User" <joeu2004 wrote in message
...
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?