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

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

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

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

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

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