returning a row number

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

Harold Good

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
 
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
 
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
 
If that is still a question, just assign it to your variable instead of
MessageBox'ing it...

YourVariable = Range("Amount_Local").End(xlDown).Row
 
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
 
Back
Top