Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
definition of the range containing content | Excel Programming | |||
Range Definition in Macro ? | Excel Programming | |||
Range definition problem | Excel Programming | |||
How come this range definition is invalid? | Excel Programming | |||
Using Cells( ) for Range definition | Excel Programming |