Formula to return position of the next Non-blank cell in a column

  • Thread starter Thread starter PCLIVE
  • Start date Start date
P

PCLIVE

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
 
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?
 
Put this in a REGULAR vba module. On the worksheet =nr(a1) or =nr(g3) etc

Function nr(x As Range)
Application.Volatile
nr = x.End(xlDown)
End Function
 
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
 
... row number ..

=MATCH(INDEX(INDIRECT(C1 &":A100"),MATCH(TRUE,INDIRECT(C1
&":A100")<>"",0),0),A:A,0)

with CSE
 
If the position (address?) of the first non-blank is known then all you need
to do is start looking for the first non-blank after the known.

Based on your sample:

="A"&INDEX(ROW(A2:A20),MATCH("*",A2:A20,0))

Result = A6

Or is there more to it than that?
 
Do you mean that wherever the first occurrence is you want the next
occurrence? If so

=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE,A1:A100<>"",0)+1):INDEX(A1:A100,100)<>"",0)+MATCH(TRUE,A1:A100<>"",0))


array entered



will give you the contents of the next non blank cell in A1:A100, if you
want the cell address


=CELL("address",INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE,A1:A100<>"",0)+1):INDEX(A1:A100,100)<>"",0)+MATCH(TRUE,A1:A100<>"",0)))


the position

=MATCH(TRUE,INDEX(A1:A100,MATCH(TRUE,A1:A100<>"",0)+1):INDEX(A1:A100,100)<>"",0)+MATCH(TRUE,A1:A100<>"",0)


these are all non volatile

Of course you might want to add a test to make sure it doesn't return an
error if there is 1 or less occurrences in the range like

=IF(COUNTA(A1:A100<=1,"",formula
 
Note: that will only work on TEXT (as your sample shows). If the next
non-blank might be either text or numeric it's not much different:

="A"&INDEX(ROW(A2:A20),MATCH(TRUE,INDEX(A2:A20<>"",,1),0))
 
Mon, 16 Jul 2007 11:07:40 -0500 from Don Guillett <dguillett1
@austin.rr.com>:
How about a macro?
Sub nextnonblankvalue()
MsgBox ActiveCell.End(xlDown)
End Sub
Click to expand...

Suppose A1 through D1 are non-empty, and E1 is empty. If you're in
A1, won't the above macro return D1 instead of the wanted B1?
 
Where did B1 come from?
This will look DOWN from the activecell. If in cell b1 it will look ONLY in
column B, A looks in A, etc.
 
Back
Top