Formula to return position of the next Non-blank cell in a column
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 |
Formula to return position of the next Non-blank 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 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 |
Formula to return position of the next Non-blank 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 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 |
Formula to return position of the next Non-blank 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 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 |
Formula to return position of the next Non-blank 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 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 |
Formula to return position of the next Non-blank 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 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 |
Formula to return position of the next Non-blank cell in a column
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? -- Biff Microsoft Excel MVP "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 |
Formula to return position of the next Non-blank 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 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 |
Formula to return position of the next Non-blank 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 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? -- Biff Microsoft Excel MVP "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 |
Formula to return position of the next Non-blank cell in a column
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)) -- Biff Microsoft Excel MVP "PCLIVE" wrote in message ... 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 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? -- Biff Microsoft Excel MVP "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 |
Formula to return position of the next Non-blank cell in a column
these are all non volatile
CELL( ) is volatile -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... 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 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 |
Formula to return position of the next Non-blank cell in a column
Yes you are correct, using address instead would fix that
-- Regards, Peo Sjoblom "T. Valko" wrote in message ... these are all non volatile CELL( ) is volatile -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... 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 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 |
Formula to return position of the next Non-blank cell in a column
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? -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
Formula to return position of the next Non-blank cell in a column
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stan Brown" wrote in message t... 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? -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com