*Resolved* Determining Longest Run of Consecutive Numbers

[TheRobster]

Hi all,

How would I get Excel to determine the longest 'run' of a given number in a list of numbers? E.g. suppose I have the following data in column A:

24
34
0
12
0
0
67
54
0
0
0
99

Now suppose I want Excel to look at that data and tell me what the longest consecutive run of zero's are. In this case it would be 3 zero's. But how does I do this?

Thanks
-Rob

[RobDog888]

No easy to do this other then a loop and a counter.

[TheRobster]

Hi RobDog,

Can you elaborate please? I don't know what you mean.

*Edit* The number I want to look at will always be zero, if this makes it any easier to do.

Thanks
-Rob

[RobDog888]

Something like iterating through the cells like so.

dim i as integer
dim iPrev as integer
 
For i = 1 to ActiveSheet.Cells.Range.SpecialCells(xlTypeCellLastCell).Row
 
    If Cells(i, 1).Value = 0 then
        'increment a continuity counter.
        if iPrev = i - 1 then
            'add to an array or some list or continuous rows.
        endif
    endif
next

I just typed it in the reply box so it probably has errors but it should get you started.

[TheRobster]

Obtained a solution from another forum. Just in case anyone needs a similar one in future, here it is:

I'm not sure if this will do, but try the following...

1) Enter the text value 'End' at the end of your list of numbers, in this case A13.

2) Enter the following formula in B1, and copy down:

=IF(A1=0,MATCH(TRUE,A1:A13<>0,0)-1,"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

3) Enter the following formula in C1:

=MAX(B1:B12)

Notice that the range used by the MATCH function includes A13, which contains the text value 'End'. You could always choose to hide this value using 'Conditional Formatting', if you wish.

Hope this helps!