## 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

### Category: Sequence

Comments(14) Filed in category: Excel, Sequence

Create number sequences in excel 2007

Create number sequences (Autofill) Example 1 Type "1" in cell B3 + Enter Right click on black dot and drag […]Comments(9) Filed in category: Excel, Sequence

Sequencing and numbering of batches in excel

Question: I have two volumes which keeps on varying in L1 & L2 head. This is the only info i […]Comments(6) Filed in category: Excel, Sequence

Find the longest/smallest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]Comments(5) Filed in category: Excel, Sequence

Comments(2) Filed in category: Excel, Sequence

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]Comments(1) Filed in category: Excel, Sequence

### 3 Responses to “Find the longest/smallest consecutive sequence of a value (vba)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.