| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning a row number
Hi,
I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
returning a row number
To answer your direct question, Selection.Row should give you that, as in
this example... Range("Amount_Local").End(xlDown).Select MsgBox Selection.Row HOWEVER, you almost never have to select a cell in order to work with it in VB. You can get the row directly like this... MsgBox Range("Amount_Local").End(xlDown).Row If you have more than one thing to do to the cell, use a With/EndWith block... With Range("Amount_Local").End(xlDown) .Value = "New Text In The Cell" .Font.Size = 18 .Font.ColorIndex = 3 End With -- Rick (MVP - Excel) "Harold Good" wrote in message ... Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
returning a row number
msgbox Range("Amount_Local").End(xlDown).row
-- HTH... Jim Thomlinson "Harold Good" wrote: Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
returning a row number
Thanks, I realize I was not clear in my description. I don't need a message
box to tell me. I should have said how can it RETURN the value of that row so I can use it to select a bigger range for sorting. Thanks again, Harold "Rick Rothstein" wrote in message ... To answer your direct question, Selection.Row should give you that, as in this example... Range("Amount_Local").End(xlDown).Select MsgBox Selection.Row HOWEVER, you almost never have to select a cell in order to work with it in VB. You can get the row directly like this... MsgBox Range("Amount_Local").End(xlDown).Row If you have more than one thing to do to the cell, use a With/EndWith block... With Range("Amount_Local").End(xlDown) .Value = "New Text In The Cell" .Font.Size = 18 .Font.ColorIndex = 3 End With -- Rick (MVP - Excel) "Harold Good" wrote in message ... Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
returning a row number
If that is still a question, just assign it to your variable instead of
MessageBox'ing it... YourVariable = Range("Amount_Local").End(xlDown).Row -- Rick (MVP - Excel) "Harold Good" wrote in message ... Thanks, I realize I was not clear in my description. I don't need a message box to tell me. I should have said how can it RETURN the value of that row so I can use it to select a bigger range for sorting. Thanks again, Harold "Rick Rothstein" wrote in message ... To answer your direct question, Selection.Row should give you that, as in this example... Range("Amount_Local").End(xlDown).Select MsgBox Selection.Row HOWEVER, you almost never have to select a cell in order to work with it in VB. You can get the row directly like this... MsgBox Range("Amount_Local").End(xlDown).Row If you have more than one thing to do to the cell, use a With/EndWith block... With Range("Amount_Local").End(xlDown) .Value = "New Text In The Cell" .Font.Size = 18 .Font.ColorIndex = 3 End With -- Rick (MVP - Excel) "Harold Good" wrote in message ... Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
returning a row number
Ahh, I see that something like this seems to do the trick:
RangeFinish = Range("Amount_Local").End(xlDown).Row Thanks very much Rick and Jim for your help, Harold "Harold Good" wrote in message ... Thanks, I realize I was not clear in my description. I don't need a message box to tell me. I should have said how can it RETURN the value of that row so I can use it to select a bigger range for sorting. Thanks again, Harold "Rick Rothstein" wrote in message ... To answer your direct question, Selection.Row should give you that, as in this example... Range("Amount_Local").End(xlDown).Select MsgBox Selection.Row HOWEVER, you almost never have to select a cell in order to work with it in VB. You can get the row directly like this... MsgBox Range("Amount_Local").End(xlDown).Row If you have more than one thing to do to the cell, use a With/EndWith block... With Range("Amount_Local").End(xlDown) .Value = "New Text In The Cell" .Font.Size = 18 .Font.ColorIndex = 3 End With -- Rick (MVP - Excel) "Harold Good" wrote in message ... Hi, I have a named range "Amount_Local" in a single column. The following line of code will find the last cell before a blank cell, which is what I want. Range("Amount_Local").End(xlDown).Select How can I get it to tell me what row it is? thanks, Harold |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Returning a column name or number | Excel Worksheet Functions | |||
| Searching and returning row number of a value | Excel Worksheet Functions | |||
| Returning a value for a number in a range | Excel Discussion (Misc queries) | |||
| Returning The Column Number ? | Excel Programming | |||
| returning max UsedRange row number | Excel Programming | |||
Linear Mode
