Find the longest/smallest consecutive sequence of a value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show you how to build a simple udf that will simplify these formulas significantly.
The following user defined function returns the size of each consecutive sequence in a range. The udf is entered in cell range B2:B25, see this picture.
The udf returns an array with the same size as the argument range. The cell range has 24 cells and the array has 24 values.
VBA code
Function CountConsVal(r As Range) Dim i As Long, s As Long Rng = r.Value For i = LBound(Rng, 1) To UBound(Rng, 1)  1 If Rng(i, 1) = Rng(i + 1, 1) Then s = s + 1 Rng(i, 1) = "" Else Rng(i, 1) = s + 1 s = 0 End If Next i Rng(UBound(Rng), 1) = s + 1 CountConsVal = Rng End Function
Array formulas
Array formula in cell E3:
Array formula in cell E4:
Array formula in cell G3:
Array formula in cell G4:
Array formula in cell E7:
Array formula in cell E8:
Build a user defined function
To build a user defined function, follow these steps:
 Press Alt + F11 to open the visual basic editor
 Click "Insert" on the menu

Click "Module"
 Copy the code above and paste it to the code module.
Explaining the user defined function
Function name and arguments
A user defined function procedure always start with "Function" and then a name. This udf has a single argument r. Variable r is a range.
Function CountConsVal(r As Range)
Declaring variables
Variable i and s are declared data type Long. Read more about Defining data types.
Dim i As Long, s As Long
Transfer values from r (range) to a Rng (variant) array
Rng = r.Value
For ... Next statement
Repeats a group of statements a specified number of times. LBound returns the lower bound of an array and UBound the upper bound.
LBound(array, dimension) UBound(array, dimension), the dimension arguemnt can be omitted if the array is a one dimensional array. In this case the Variant Rng is a two dimensional array despite the fact that the range r is a one dimensional array. UBound(Rng, 1) returns the number of rows in Rng array.
For i = LBound(Rng, 1) To UBound(Rng, 1)  1 ... Next i
If ... then... Else ... End If
Check if the current value (i) is equal to the next value (i+1)
If Rng(i, 1) = Rng(i + 1, 1) Then
Add number 1 to variable s
s = s + 1
Delete value in array
Rng(i, 1) = ""
Assign a value (s + 1) to array variable Rng(i, 1)
The current value in the array is equal to s + 1
Rng(i, 1) = s + 1
Assign 0 (zero) to variable s
s = 0
Assign a value (s + 1) to the last value in the array Rng(UBound(Rng), 1)
Rng(UBound(Rng), 1) = s + 1
The udf returns an array of values
CountConsVal = Rng
End a udf
A function procedure ends with this statement.
End Function
Recommended reading

Find the longest/smallest consecutive sequence of a value
 Count the number of cells within a range that match multiple comma separated values
 Excel udf: Lookup and return multiple values concatenated into one cell
Download excel * .xlsm file
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The [โฆ]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to execute a VBA macro using a drop down list. The drop down list contains multiple [โฆ]
Have you ever seen dashboards where you can click a shape and a picture shows up. If you click it [โฆ]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies [โฆ]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The [โฆ]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The [โฆ]
Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right [โฆ]
Excel has a great builtin versatile tool for creating number sequences. In some situations, however, you need to rely on [โฆ]
9 Responses to โFind the longest/smallest consecutive sequence of a value [VBA]โ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
How can i do this with colorformatted cells?. All the cells have the same value (1) the range is A1:A99999 and some rows are colorformatted with red using a different sheeth(table) and vba code.
Please helpme, If I change the numnber 1 by zero.
1. How many consecutive zero 2nd logest?
2. How many consecutive zero from left to rỉght in a row?
Thanks
Okido
The values in column B show how many consecutive values there are in column A, for each group.
I am a newbie at this. I have been trying to follow your instruction on how to do it but I can not seem to do it right. Copypasting the udf down the rows does not seem to work for me. Instead of alphabets in column A, I used their corresponding ordinal numbers, such A = 1, B = 2, C = 3. For column B, instead of just the number, it must include the letter it counted, like "3 C's" in B25 cell which is "3" only. I hope you could help me with this. Thank you.
liam,
Copypasting the udf down the rows does not seem to work for me.
No, the following steps allows you to enter the UDF as an array formula.
1. Select cell range B2:B25.
2. Type: =CountConsVal(A2:A25)&" "&CHAR(A2:A25+64)&"'S "
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. Release all keys
Instead of alphabets in column A, I used their corresponding ordinal numbers, such A = 1, B = 2, C = 3. For column B, instead of just the number, it must include the letter it counted, like "3 C's" in B25 cell which is "3" only.
See formula in step 2 above.
Thank you so much for the help. It worked perfectly!
Hi,
The formula does not work when the data is displayed horizontally.
Hi. I am trying to generate a chart that show how many time each number in the array followed the others.
My array is 6 columns by 2600 rows is a random list the first column the numbers go from 1 to 48, second 249; 350; 451; 552; 653.
thank you for reading this post.
Jeyner Lopez,
Can you explain "how many time each number in the array followed the others." in greater detail?