Count X amount of numbers between #1 occurs

Best way to explain this question is by example
I have a column of numbers

Column X
1
2
3
4
1
4
5
6
7
8
2
1

The biggest gap in between the number 1 occurring is 6 times as bold above. How can i using excel formula or VBA highlight this, especially in a much larger range of data (like 15000 Lines)

There's probably a more elegant way, but I find helper columns easiest to see what's going on.
Suppose your list starts at A2.
In B2, put
=IF(A2=1,1,b1+1)

Copy that down, and take the MAX of that column, =MAX(B:B)

You might need to change the first cell if A2<>1 (might give an error, depending on what's in b1!).

For me that resturn all empty cells after last occure...

Maybe this:

=MAX(FREQUENCY(IF(A1:A15000<>"";ROW(A1:A15000));IF(A1:A15000=1;ROW(A1:A15000))))

See? daddylonglegs is much more elegant!

Your approach is not bad either.