Using Address in Range Definition??
 

Hi Folks,

Need some more help here.....Below is macro used to find the address of the
last cell in the last column used and store that address in variable "R"...

Sub lastcell()

Dim R As String

Cells(Rows.Count, "B").End(xlUp).Select
Selection.End(xlToRight).Select
R = Selection.Address
MsgBox (R)

End Sub

MsgBox will show the Address of the last cell. Assume last cell is H8,
Msgbox will display "$H$8".....This address is also stored in "R".

My question is how do I use the data stored in "R" in a Range
call....similar to the following...

Range("B4:R").Select

Thanks in advance,

Don

Using Address in Range Definition??
 

Try this:

Range("B4:" & R).Select


--
renegan
------------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
View this thread: http://www.excelforum.com/showthread...hreadid=528773

Using Address in Range Definition??
 

Concatenate like you would any other string
Range("B4:"&R).Select

Also - just as a pointer, it is not usually necessary to select cells/ranges
in order to work with them. With larger macros, it can make a difference in
amount of time it takes to process.

Sub lastcell()
Dim R As String

R = Cells(Rows.Count, "B").End(xlUp).End(xlToRight).Address
MsgBox (R)

End Sub

"Don G" wrote:

Hi Folks,

Need some more help here.....Below is macro used to find the address of the
last cell in the last column used and store that address in variable "R"...

Sub lastcell()

Dim R As String

Cells(Rows.Count, "B").End(xlUp).Select
Selection.End(xlToRight).Select
R = Selection.Address
MsgBox (R)

End Sub

MsgBox will show the Address of the last cell. Assume last cell is H8,
Msgbox will display "$H$8".....This address is also stored in "R".

My question is how do I use the data stored in "R" in a Range
call....similar to the following...

Range("B4:R").Select

Thanks in advance,

Don

Using Address in Range Definition??
 

Hi renegan,

Thank you very much for the quick reply....works like a charm.....:)

Don

"renegan" wrote:


Try this:

Range("B4:" & R).Select


--
renegan
------------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
View this thread: http://www.excelforum.com/showthread...hreadid=528773

Using Address in Range Definition??
 

Hi JMB,

Thank you also for the very quick reply....and for the pointer.

Don

"JMB" wrote:

Concatenate like you would any other string
Range("B4:"&R).Select

Also - just as a pointer, it is not usually necessary to select cells/ranges
in order to work with them. With larger macros, it can make a difference in
amount of time it takes to process.

Sub lastcell()
Dim R As String

R = Cells(Rows.Count, "B").End(xlUp).End(xlToRight).Address
MsgBox (R)

End Sub

"Don G" wrote:

Hi Folks,

Need some more help here.....Below is macro used to find the address of the
last cell in the last column used and store that address in variable "R"...

Sub lastcell()

Dim R As String

Cells(Rows.Count, "B").End(xlUp).Select
Selection.End(xlToRight).Select
R = Selection.Address
MsgBox (R)

End Sub

MsgBox will show the Address of the last cell. Assume last cell is H8,
Msgbox will display "$H$8".....This address is also stored in "R".

My question is how do I use the data stored in "R" in a Range
call....similar to the following...

Range("B4:R").Select

Thanks in advance,

Don