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

tuqiuchiaa

New Member
Joined
Mar 12, 2018
Messages
3
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

ABCDEFResult
2120003
002032002
00110012
102312211

<tbody>
</tbody>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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))))
 
Upvote 0
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))))
Aladin,

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:
Upvote 0
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]
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top