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.

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

Back to top

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:

Back to top

3. Where to put the code?

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

1. Press Alt + F11 to open the visual basic editor.
2. Press with left mouse button on "Insert" on the menu.
3. Press with left mouse button on "Module".
   
4. Copy the code above and paste it to the code module.

Back to top

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

Get the Excel file

Longest-consecutive-sequence.xlsm

Back to top

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

Back to top

If then else statement category

Excel calendar [VBA]
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

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

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

Macro category

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]

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 […]

Sequence category

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

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

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

Excel categories

Comments (10)