Leftmost/Rightmost Cell

[Lena]

How can i get the value of the rightmost or the leftmost
cell from a specific row if the value is not empty ??

Thanks
Lena

 

[Frank Kabel]

Hi Lena
one way: Enter the following array formulas (entered with
CTRL+SHIFT+ENTER):
=INDEX(1:1,MAX(IF(1:1<>"",COLUMN(1:1))))
and
=INDEX(1:1,MIN(IF(1:1<>"",COLUMN(1:1))))

 

[RC-]

Here's a start:


Sub GetMyVal()

Selection.End(xlToLeft).Select
x = ActiveCell.Value
Selection.End(xlToRight).Select
y = ActiveCell.Value

End Sub

 

[Jason Morin]

To get the value furthest to the left in row 1:

=INDEX(1:1,MIN(IF(1:1<>"",COLUMN(1:1))))

Array-entered (meaning press ctrl/shift/enter).

Furthest to the right:

=INDEX(1:1,MAX(IF(1:1<>"",COLUMN(1:1))))

Also array-entered.

HTH
Jason
Atlanta, GA