How to calculate A1-style ranges

[Guest]

How do you create a reference to a range where you need to calculate the bottom right-hand corner of the range? For example, I know the range starts at J8, I know the right-hand edge of the range is column L, and I have a variable that contains the number of rows. I need a reference that looks like:
Range ("J8:L230")
but using the variable instead of 230. How do I do this?

 

[dk]

Hi,

There are various methods you can use:- How about using the Resize property e.g.

Sub ExtendRange()
Dim rngeLeftTopCell As Range, rngeNewRange As Range
Dim lngNumberOfColumns As Long, lngNumberOfRows As Long

'This is the top left cell of the current selection
Set rngeLeftTopCell = Range("J8")

'Number of rows and columns you want the range increased by
lngNumberOfRows = 223
lngnumberofcols = 3

Set rngeNewRange = rngeLeftTopCell.Resize(lngNumberOfRows, lngnumberofcols)

MsgBox rngeNewRange.Address


End Sub

HTH,
D
This message was edited by dk on 2002-02-17 04:12

 

[Guest]

If your variable is represented by the letter v :-
Range ("J8:L" & v)

 

[Guest]

Two excellent solutions. Thank you both.