try:

=INDEX(INDIRECT(C1 &":A1000"),MATCH(TRUE,INDIRECT(C1 &":A1000")<"",0),0)


Enter with Ctrl+Shift+Enter

C1 contains address of the first blank cell AFTER your reference cell e.g A2
in your example

"PCLIVE" wrote:

I had already thought of that, which is pretty easy. But I wanted to try
and achieve this with a formula since I have no other reasons to run code
here.

Any other ideas?

"Don Guillett" wrote in message
...
How about a macro?
Sub nextnonblankvalue()
MsgBox ActiveCell.End(xlDown)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PCLIVE" wrote in message
...
Given the position of a non-blank cell in a column, is there a way to
return the position of the next non-blank cell in the same column?

Example:
The given position of A1 is not blank. There is an unknown number of
consecutive spaces (blank cells) that follow in the same column. Is
there a formula that will tell me the position of the next non-blank
cell?

A
1 Test
2
3
4
5
6 UnknownData

The formula should be able to somehow return the row number or position
of the next non-blank cell, in this case would be row 6 or cell A6. Keep
in mind that the next value is unknown. Is this possible?

Thanks,
Paul