Last Value in a row or column

  • Thread starter Thread starter Excel Dummy
  • Start date Start date
E

Excel Dummy

Hi All,

I need a formula or User Defined Function that will allow me to find
the last input cell in a row of variable width data.

This value will then be shown in a cell of my choice.

Thanks

Excel Dummy
 
Hi - how about this?

Public Function Findlastcell(MyAddress As Range)

Application.volatile
Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Address

End Function


You use it by entering = Findlastcell(B5) (assuming B5 is in the row you
want to find the last address of)

Please note that this does not work if the last cell is in fact in column
IV. You could include an IF statement to check for that, but it would
complicate things

Geoff
 
Thanks Geoff,

The formula you provided gives me the cell reference.

But how do I make it so that it gives me the actual value in that
cell.

Many thanks

Excel Dummy
 
Hi All,

I need a formula or User Defined Function that will allow me to find
the last input cell in a row of variable width data.

This value will then be shown in a cell of my choice.

Thanks

Excel Dummy
Click to expand...

For a formula approach, you can use the *array-entered* formula:

=INDEX(3:3,1,MAX(NOT(ISBLANK(3:3))*COLUMN(3:3)))

for the last input cell in row 3. (Change 3:3 to reflect the row of interest).

To *array-enter*, hold down <ctrl><shift> while hitting <enter>. XL will place
braces {...} around the formula.

If your data entry will always be either numbers or text, you could use a
somewhat simpler formula (also *array-entered*):

=INDEX(3:3,1,MAX(ISTEXT(3:3)*COLUMN(3:3)))

or

=INDEX(3:3,1,MAX(ISNUMBER(3:3)*COLUMN(3:3)))


--ron
 
I prefer this one (testing last value in Column A):
Array-Entered (Ctrl+Shift+Enter)
In Cell B1:
=OFFSET(A1,MAX(NOT(ISBLANK(A1:A1000))*ROW(A1:A1000))-1,0)
HTH
 
Excel Dummy said:
Thanks Geoff,

The formula you provided gives me the cell reference.

But how do I make it so that it gives me the actual value in that
cell.
Click to expand...
Try this:

Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Text

Or this if you want the last entry in a column:

Findlastcell = Cells(65536,MyAddress.Column).End(xlUp).Text


BTW I am impressed with the ingenuity of Ron and Jmay's formulas. It just
goes to show that there are usually lots of ways of doing the same thing in
Excel.

If you are going to use the formula a lot in the future you might be better
off creating a user-defined function, because it will be easier to see what
your spreadsheet is doing.

Geoff
 
Public Function Findlastcell(MyAddress As Range)

Application.Volatile
Findlastcell = Cells(MyAddress.Row, 256).End(xlToLeft).Value

End Function

It works perfectly well in lets say file1.xls until I open another file
(file2.xls).

The formula then takes the values from file2.xls. I can rectify this by
going back to file1.xls and then pressing F9.

How do I make the code above specific to file1.xls only.


Excel Dummy.
 
Back
Top