Leftmost/Rightmost Cell

  • Thread starter Thread starter Lena
  • Start date Start date
L

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
 
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))))
 
Here's a start:


Sub GetMyVal()

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

End Sub
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Leftmost and Rightmost Cell 3
Excel Columns moving up and down on their own in a worksheet. What Goes? 2
How do I reference contents of a cell at the bottom of a column? 2
How do I lookup a table from right to left ? 1
Formula help 5
Next-To-Last, Non-Blank, Rightmost Value in a Row 6
VB.Net Counterpart to RIGHT$ and LEFT$ vb6 functions??? 2
how to delete the 4 rightmost digits from a cell 4

Back
Top