## Find the most/least consecutive repeated 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.

#### Table of Contents

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.

## 1. VBA code

```
'Name User Defined Function
Function CountConsVal(r As Range)
'Dimension variables and declare data types
Dim i As Long, s As Long
'Save value in cell range r to variable Rng
Rng = r.Value
'Iterate through values in array variable Rng
For i = LBound(Rng, 1) To UBound(Rng, 1) - 1
'Check if next value is equal to current value
If Rng(i, 1) = Rng(i + 1, 1) Then
'Add 1 to numbers stored in variable s
s = s + 1
'Clear container in array variable Rng
Rng(i, 1) = ""
'Go here if next value is NOT equal to current value
Else
'Add 1 to variable s and save to container in array variable Rng
Rng(i, 1) = s + 1
'Save 0 to variable s
s = 0
End If
Next i
'Add 1 to variable s and then save result to the last container in array variable Rng
Rng(UBound(Rng), 1) = s + 1
'Return values to worksheet
CountConsVal = Rng
End Function
```

## 2. 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:

## 3. Where to put the code?

To build a user-defined function in your workbook, follow these steps:

- Press Alt + F11 to open the visual basic editor.
- Press with left mouse button on "Insert" on the menu.
- Press with left mouse button on "Module".

- Copy the code above and paste it to the code module.

## 4. Explaining the User Defined Function

#### 4.1 Function name and arguments

A user-defined function procedure always starts with "Function" and then a name. This UDF has a single argument r. Variable r is a range.

Function CountConsVal(r As Range)

#### 4.2 Declaring variables

Variable i and s are declared data type Long. Read more about Defining data types.

Dim i As Long, s As Long

#### 4.3 Transfer values from r (range) to a Rng (variant) array

Rng = r.Value

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

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

#### 4.6 Add number 1 to variable s

s = s + 1

#### 4.7 Delete value in array

Rng(i, 1) = ""

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

#### 4.9 Assign 0 (zero) to variable s

s = 0

#### 4.10 Assign a value (s + 1) to the last value in the array Rng(UBound(Rng), 1)

Rng(UBound(Rng), 1) = s + 1

#### 4.11 The udf returns an array of values

CountConsVal = Rng

#### 4.12 End a udf

A function procedure ends with this statement.

End Function

### Recommended reading

### If then else statement category

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]

This article demonstrates how to use the If ... Then statement in Visual Basic for Applications (VBA). You can combine […]

### Macro category

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

In this post I am going to show how to create a new sheet for each airplane using vba. The […]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]

This article describes how to create an interactive chart, the user may press with left mouse button on a button […]

Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]

In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]

Today I would like to share with you these small event handler procedures that make it easier for you to […]

This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]

This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]

### Sequence category

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]

This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 10 Responses to “Find the most/least consecutive repeated 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. Copy-pasting 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,

Copy-pasting 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 2-49; 3-50; 4-51; 5-52; 6-53.

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?

Hi,

I am trying to find the longest consecutive sequence of different words in a column before one of the words is repeated, then display a list of those words. Hoping someone could please assist with an Excel formula for this. Thank-you.