This post explains how to return an array of numbers using an excel formula in excel.

1. Create an Array of Numbers using Formula

If you want to create an array of numbers, you can use a combination of the ROW function, the INDIRECT function or OFFSET function or INDEX function or MIN function to create a new formula.

For example, to create an array of numbers like {1;2;3;4;5}, you can use the following formulas:

=ROW(B1:B5)-MIN(ROW(B1:B5))+1

=ROW(INDIRECT("1:5"))

=ROW(INDEX(A:A,1):INDEX(A:A,5))

={1;2;3;4;5}

=ROW(B1:B5)

When you entered the above formula into a single cell, it just display only the first element in the array. And you need to press F9 to display the actual array result in the formula bar.

2. Create an Array of Numbers using VBA Code

the second method, we’ll explore how to use VBA code to swiftly generate an array of numbers programmatically.

For a more automated approach, VBA proves invaluable.

 Press ‘Alt + F11‘ to open the Visual Basic for Applications editor.

In the VBA editor, click on Insert in the menu and select Module to create a new module.

Copy and paste the following VBA code into the module:

Sub GenerateNumberArray()
    Dim i As Integer
    Dim arrayStart As Integer
    Dim arrayEnd As Integer
    Dim destinationCell As Range
    ' Prompt for inputting array start and end numbers
    arrayStart = InputBox("Enter the starting number for the array:", "Array Start Number Input", 1)
    arrayEnd = InputBox("Enter the ending number for the array:", "Array End Number Input", 5)
    ' Prompt for selecting destination cell
    On Error Resume Next
    Set destinationCell = Application.InputBox("Select the destination cell for the array:", Type:=8)
    On Error GoTo 0
    If Not destinationCell Is Nothing Then
        For i = 0 To arrayEnd - arrayStart
            destinationCell.Offset(i, 0).Value = arrayStart + i
        Next i
    Else
        MsgBox "Operation canceled. No destination cell selected."
    End If
End Sub

Close the VBA editor by clicking the “X” button or pressing Alt + Q.

Press Alt + F8 to open the “Macro” dialog box.

Select GenerateNumberArray from the list.

Click Run.

Input the starting number when prompted.

Input the ending number when prompted.

Select the destination cell where you want the array to start.

The VBA code will populate the selected range with a sequential array of numbers from the starting to the ending number.

3. Video: Create an Array of Numbers

This Excel video tutorial focusing on the creation of an array of numbers. In this session, we’ll explore two distinct methods – a formula-based approach utilizing the ROW function and a VBA code.

4. Related Formulas

• Find the Relative Position in a Range or Table
  If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
• Get the First Row Number in a Range
  If the ROW function use a Range as its argument, it only returns the first row number.You can also use the ROW function within the MIN function to get the first row number in a range. You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range.…
•  Get the Last Row Number in a Range
  If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…

Related Functions

• Excel INDEX function
  The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel ROW function
  The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel MIN function
  The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
• Excel INDIRECT  function
  The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….