Iam using the following to get values from the last cells
in a mamed range:

Bal = Range("MyRange").End(xlToRight).Offset(0, 0).Value
(Thanks RADO)

This works fine for the first row. I have a named range
$A$1:$G$10, How can I get this to work on the row the
active cell is in?

Try:

Dim Bal
Bal = ActiveCell.End(xlToRight)

then if you want to proceed to the next row use

Activecell.Offset(1,0).Activate


This can also be put in a loop to cycle thru the list and get the las
cell of each row.


Note: the Offset(0,0) does NOTHING - It just means do not offset an
rows or colums

HT

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Gocush
Thanks for your reply, I know about the offset what I am
trying to achieve is:

How can I get this to work on only the row the active cell
is in?

-----Original Message-----

Try:

Dim Bal
Bal = ActiveCell.End(xlToRight)

then if you want to proceed to the next row use

Activecell.Offset(1,0).Activate


This can also be put in a loop to cycle thru the list and
get the last
cell of each row.


Note: the Offset(0,0) does NOTHING - It just means do
not offset any
rows or colums

HTH


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Bal = ActiveCell.End(xlToRight

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Bob,

I get the same results with your code as with my suggestion. Can yo
explain the difference?

Thank

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

The difference is that you go right from the activecell to the last value,
whereas I go all the way to the right (Cells(Activecell.Row,Columns.Count))
and then work back to the last value.

It returns different results if the row has blanks in it. Consider these
values
18 21 33 44 blank 55
Your code will find the last value in the contiguous cells from the
activecell, that is 44, whereas mine will find the very last value, that is
55.

Also, if the activecell is past the row data, yours returns an empty value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"gocush" wrote in message
...

Bob,

I get the same results with your code as with my suggestion. Can you
explain the difference?

Thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Keith,

Try

bal = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Keith Robinson" wrote in message
...
Iam using the following to get values from the last cells
in a mamed range:

Bal = Range("MyRange").End(xlToRight).Offset(0, 0).Value
(Thanks RADO)

This works fine for the first row. I have a named range
$A$1:$G$10, How can I get this to work on the row the
active cell is in?

Thanks bob
That has the desired effect

Keith Robinson
Sydney Austalia ( Ex North Yorkshire)

-----Original Message-----
Keith,

Try

bal = Cells(ActiveCell.Row, Columns.Count).End
(xlToLeft).Value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Keith Robinson"
wrote in message
...
Iam using the following to get values from the last
cells
in a mamed range:

Bal = Range("MyRange").End(xlToRight).Offset(0, 0).Value
(Thanks RADO)

This works fine for the first row. I have a named range
$A$1:$G$10, How can I get this to work on the row the
active cell is in?


.