Last cell in a range

[Keith Robinson]

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?

 

[gocush]

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

 

[Keith Robinson]

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?

 

[gocush]

Bal = ActiveCell.End(xlToRight

 

[Bob Phillips]

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)

 

[gocush]

Bob,

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

Thank

 

[Bob Phillips]

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)

 

[Keith Robinson]

Thanks bob
That has the desired effect

Keith Robinson
Sydney Austalia ( Ex North Yorkshire)