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

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
 
D

Don Guillett

How about a macro?
Sub nextnonblankvalue()
MsgBox ActiveCell.End(xlDown)
End Sub
 
P

PCLIVE

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?
 
D

Don Guillett

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
 
T

Toppers

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
 
T

Toppers

... row number ..

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

with CSE
 
T

T. Valko

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?
 
P

Peo Sjoblom

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
 
T

T. Valko

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))
 
S

Stan Brown

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

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?
 
D

Don Guillett

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.
 

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

Top