# Count the first non-zero Consecutive Cells until another 0 appears

#### tuqiuchiaa

##### New Member
Hi,
I'm trying to figure out a way to count the number of cells in the first non-zero Consecutive cells.
Been reading all the posts about consecutive cells posts, but couldn't get anything working.
Could anyone help? Thanks in advance!
The desired results are listed below

 A B C D E F Result 2 1 2 0 0 0 3 0 0 20 32 0 0 2 0 0 1 10 0 1 2 1 0 231 2 2 1 1

<tbody>
</tbody>

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Joe4

Welcome to the Board!

I used VBA to create my own function to do it. Here is what it looks like:
Code:
``````Function Consec(myRange As Range) As Long
'   Count the first non-zero consecutive cells until another 0 appears

Dim cell As Range
Dim ct As Long

For Each cell In myRange
If cell = 0 Then
If ct > 0 Then Exit For
Else
ct = ct + 1
End If
Next cell

Consec = ct

End Function``````
So then you would just use it like any other function in Excel, i.e.
=Consec(A2:F2)

• tuqiuchiaa

#### tuqiuchiaa

##### New Member
Hi Joe, Thank you very much for the reply. I can do it in VBA too, but the reason why I want to stick with Excel Formula is that no one else in the team knows VBA, so Excel formulas is better in term of re-usability of this sheet and for the sake of saving me the effort of changing VBA code for them every week.
Thanks,
Ben

#### Joe4

Why would you need to change the VBA code every week?
The Function I gave you is very generic, and shouldn't need any updating, just like any other Excel function.
Also, no one needs to know/understand VBA in order to use that function. It really just is "plug and play".

I am sure that there probably is a formulaic approach that will work, but those type of array formulas really are not my specialty (which is why I usually go the VBA route).
Maybe someone else will be able to provide a formulaic solution.

• tuqiuchiaa

#### tuqiuchiaa

##### New Member
You know what. My bad. This should work, thank you so much.

#### Joe4

You are welcome.

##### MrExcel MVP
In H2 control+shift+enter, not just enter, and copy down:

=MAX(FREQUENCY(IF(ISNUMBER(A2:F2),IF(A2:F2=0,COLUMN(A2:F2))),IF(1-(A2:F2=0),COLUMN(A2:F2))))

#### Joe4

In H2 control+shift+enter, not just enter, and copy down:

=MAX(FREQUENCY(IF(ISNUMBER(A2:F2),IF(A2:F2=0,COLUMN(A2:F2))),IF(1-(A2:F2=0),COLUMN(A2:F2))))

I came across an issue with this solution.
It works fine as long as there are BOTH zeroes and numbers.
But, if all six cells are 0, it will return a 6 (instead of 0).
And if all six cells are numbers other than 0, it will return 0 (instead of 6).

Last edited:

#### Rick Rothstein

##### MrExcel MVP
Welcome to the Board!

I used VBA to create my own function to do it. Here is what it looks like:
Code:
``````Function Consec(myRange As Range) As Long
'   Count the first non-zero consecutive cells until another 0 appears

Dim cell As Range
Dim ct As Long

For Each cell In myRange
If cell = 0 Then
If ct > 0 Then Exit For
Else
ct = ct + 1
End If
Next cell

Consec = ct

End Function``````
So then you would just use it like any other function in Excel, i.e.
=Consec(A2:F2)
For those who might find this interesting, and assuming the range being given to this function is always a horizontal range of contiguous cells, the above UDF can be written as a one-liner...
Code:
``````[table="width: 500"]
[tr]
[td]Function Consec(myRange As Range) As Long
Consec = Len(Split(Application.Trim(Join(Evaluate("IF(" & myRange.Address & "=0,"" "",""X"")"), "")))(0))
End Function[/td]
[/tr]
[/table]``````

#### Joe4

For those who might find this interesting, and assuming the range being given to this function is always a horizontal range of contiguous cells, the above UDF can be written as a one-liner...
Code:
 Function Consec(myRange As Range) As Long Consec = Len(Split(Application.Trim(Join(Evaluate("IF(" & myRange.Address & "=0,"" "",""X"")"), "")))(0)) End Function

<tbody>
</tbody>

I think you need to add some error handling code in there. That function blows up if all the cells are 0 (it returns the #VALUE ! error).

1,095,176
Messages
5,442,836
Members
405,201
Latest member
kashyap44

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...