Home 
Search 
Today's Posts 
#1




Formula to return position of the next Nonblank cell in a column
Given the position of a nonblank cell in a column, is there a way to return
the position of the next nonblank 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 nonblank 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 nonblank 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 
#2




Formula to return position of the next Nonblank cell in a column
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 nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
#4




Formula to return position of the next Nonblank cell in a column
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  Don Guillett Microsoft MVP Excel SalesAid Software "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 nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
#5




Formula to return position of the next Nonblank cell in a column
That's pretty cool Don. I think I can make this work.
Thanks a bunch. Paul "Don Guillett" wrote in message ... 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  Don Guillett Microsoft MVP Excel SalesAid Software "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 nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
#6




Formula to return position of the next Nonblank cell in a col
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 nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
#7




Formula to return position of the next Nonblank cell in a col
... row number ..
=MATCH(INDEX(INDIRECT(C1 &":A100"),MATCH(TRUE,INDIRECT(C1 &":A100")<"",0),0),A:A,0) with CSE "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 nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
#8




Formula to return position of the next Nonblank cell in a column
If the position (address?) of the first nonblank is known then all you need
to do is start looking for the first nonblank 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?  Biff Microsoft Excel MVP "PCLIVE" wrote in message ... Given the position of a nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
#9




Formula to return position of the next Nonblank cell in a column
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,10 0)<"",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  Regards, Peo Sjoblom "PCLIVE" wrote in message ... Given the position of a nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
#10




Formula to return position of the next Nonblank cell in a column
That's pretty interesting too. I will play around with this.
Thanks, Paul "T. Valko" wrote in message ... If the position (address?) of the first nonblank is known then all you need to do is start looking for the first nonblank 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?  Biff Microsoft Excel MVP "PCLIVE" wrote in message ... Given the position of a nonblank cell in a column, is there a way to return the position of the next nonblank 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 nonblank 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 nonblank 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 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Return next nonBlank Cell in a Column ?  Excel Worksheet Functions  
Return Numeric Value to their Matched Value Position in Single Column  Excel Worksheet Functions  
Formula to return cell position  Excel Worksheet Functions  
Excel formula to find position of the contents of a cell within a column.  Excel Discussion (Misc queries)  
find the first blank cell in a range and return me it's position  Links and Linking in Excel 