Value of last cell in range

[Dutch1956]

hi All,

how can I return the last value in a range?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	12	15	17	20				4	9	8
2			3	6	8				12	16	19	36

<tbody>
</tbody>

Based on the above, I would like to return the values in D1, J1, E2and L2.
Using =LOOKUP(9.99E+307,H30:U30) will only return the value of J8.
I am looking for a formula that will return both.
Thanks for you help.

Bert

 

[Special-K99]

I don't know about a formula but this will do it.

Runs from row1 column1 to row 30 column 21 (U30) with result in column V

Sub k1()
For i = 1 To 30
k = ""
For j = 1 To 21
If Cells(i, j) <> "" And Cells(i, j + 1) = "" Then k = k & Cells(i, j) & ", "
Next j
If k <> "" Then k = Left(k, Len(k) - 2)
Cells(i, 22) = k
Next i
End Sub

 

[Dutch1956]

Thanks for the quick response. Your macro works perfect but I prefer to have formula so that the user do not have to run the macro every time the data changes. Also, the result of the macro put both values in one cell, like 20,8 or 8,36. I would like the result in separate cells.

Bert.

 

[Andrew Poulsom]

In M1 copied down:

=INDEX(A1:K1,MATCH(1,INDEX(ISNUMBER(A1:K1)*(B1:L1=""),),FALSE))

and in N1 copied down:

=LOOKUP(9.99999999999999E+307,A1:L1)

 

[Dutch1956]

Hi Andrew,

once again you helped me out again. Your formula is exactly what I was looking for. Thank you so much.

Bert.