Excel SEQUENCE Macro: Generate Number Series in VBA

VBA & macros · Updated November 18, 2026

If you want a sequence of numbers in Excel, the simplest option is usually the SEQUENCE worksheet function, not a macro: enter =SEQUENCE(5) in a blank cell to spill the numbers 1 through 5. If you specifically need VBA, a short macro can write a chosen starting value and increment down the sheet; the example below does that from the active cell.

SEQUENCE function or Excel macro?

The word “sequence” can refer to two different Excel tools. SEQUENCE is a built-in worksheet function that returns an array of consecutive numbers. A macro is a procedure written in Visual Basic for Applications (VBA), which can perform actions such as filling cells, formatting a range, or repeating a workflow.

For a simple list that should update or spill into nearby empty cells, the function is generally the better fit: it is quick to enter, does not require macro permissions, and recalculates when its inputs change. A macro is useful when a user needs to run a repeatable task, work with older Excel versions that lack the function, or combine number generation with other actions.

In Microsoft 365 Excel for Windows, SEQUENCE is available, as it is in Excel 2021 and Excel 2024. It is not available in older perpetual versions such as Excel 2019. The VBA example is intended for desktop Excel; Excel for the web can open macro-enabled workbooks but cannot create, edit, or run VBA macros.

How the SEQUENCE function works

The function has four arguments: rows, columns, start, and step. Rows is required; the other arguments are optional. If you leave out columns, start, or step, Excel uses 1 for each omitted value. The result is a dynamic array: Excel places the formula in one cell and spills its results into the neighboring cells.

With A1:A5 empty and the formula entered in A1, =SEQUENCE(5) returns 1, 2, 3, 4, and 5 down the column. With A1:E1 empty and the formula entered in A1, =SEQUENCE(1,5,2,2) returns 2, 4, 6, 8, and 10 across the row. These examples use a five-cell output area, so keep that area clear of values or merged cells.

For a custom count, start, and increment, put this in A1 while A1:A5 is blank: =SEQUENCE(5,1,10,3). It returns 10, 13, 16, 19, and 22. The first argument sets five rows, the second sets one column, the third starts at 10, and the fourth adds 3 each time.

Rows and columns need to be positive whole numbers. A negative step is allowed, so a descending list can be generated. For example, with A1:A5 clear, =SEQUENCE(5,1,10,-2) returns 10, 8, 6, 4, and 2. If a spill range is blocked, Excel displays a spill error instead of placing the full array.

When a VBA macro is useful

A macro makes sense when the sequence is one step in a larger routine. For example, staff might need to create numbered records in a template every week, or a workbook might need to fill a fixed range before printing. A macro can also write values directly into cells rather than leaving a dynamic-array formula behind.

Use the sample below when you want to enter a starting number, an increment, and a count, then write the results downward from the currently selected cell. It writes values to the active cell and the cells below it. It does not ask before overwriting existing contents, so select an empty starting cell and check the destination range first.

Sub FillNumberSequence()
    Dim startValue As Double
    Dim stepValue As Double
    Dim itemCount As Long
    Dim i As Long
    Dim firstCell As Range
    Dim answer As Variant

    ' Ask for the first number.
    answer = Application.InputBox("Starting number:", "Create sequence", 1, Type:=1)
    If VarType(answer) = vbBoolean Then Exit Sub
    startValue = CDbl(answer)

    ' Ask for the difference between each number.
    answer = Application.InputBox("Step between numbers:", "Create sequence", 1, Type:=1)
    If VarType(answer) = vbBoolean Then Exit Sub
    stepValue = CDbl(answer)

    ' Require a positive whole-number count.
    answer = Application.InputBox("How many numbers?", "Create sequence", 10, Type:=1)
    If VarType(answer) = vbBoolean Then Exit Sub
    If answer < 1 Or answer <> Fix(answer) Then
        MsgBox "Enter a positive whole number for the count."
        Exit Sub
    End If
    itemCount = CLng(answer)

    ' Fill downward starting at the active cell.
    Set firstCell = ActiveCell
    For i = 1 To itemCount
        firstCell.Offset(i - 1, 0).Value = startValue + (i - 1) * stepValue
    Next i
End Sub

For example, enter 10 as the starting number, 3 as the step, and 5 as the count. Starting from A1, the macro writes 10, 13, 16, 19, and 22 in A1:A5. The count is the number of values, not the final row number. A negative step, such as -2, creates a descending sequence.

How to add and run the macro in Excel

  1. Save a copy of the workbook before running any macro. VBA changes cannot be reversed with Ctrl+Z.
  2. On Windows, open the VBA editor with Alt+F11. In the editor, choose Insert > Module, then paste the code into the module window.
  3. Return to Excel, select the blank cell where the first number should go, and run the macro from Developer > Macros. Select FillNumberSequence and choose Run.
  4. Enter the start, step, and count when prompted. Review the filled range before saving.
  5. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code.

If the Developer tab is missing on Windows, use File > Options > Customize Ribbon, select the Developer check box, and choose OK. On Mac, the Visual Basic Editor and macro tools are available in desktop Excel, but keyboard shortcuts and ribbon details can differ; use the Developer tab or Excel’s Tools > Macro menu if a Windows shortcut does not apply. Excel for the web does not run VBA, so open the workbook in the desktop app to use this macro.

Choosing between a function, macro, and Fill Series

Choose based on how the list will be maintained, not just on how it is first created. A function keeps the sequence tied to its parameters, a macro automates actions, and Excel’s Fill Series command is a convenient one-time tool.

  • Use SEQUENCE for a live, formula-based list, especially when the desired length or step may change. Change the formula arguments and the spilled result updates.
  • Use VBA when generating the list belongs in a repeatable workflow or must be combined with other workbook actions. Consider who will need to enable or maintain the macro.
  • Use Fill Series for a quick manual sequence without writing a formula or code. Enter an initial value, select the cells to fill, then use Home > Editing > Fill > Series and choose the direction and step settings.
  • Use the fill handle for simple patterns. Enter the first values that establish the pattern, select them, and drag the small square at the selection’s lower-right corner. Excel may infer a simple number pattern, but review the result because a single starting value can be copied rather than incremented.

There is no requirement to use VBA just because a spreadsheet task is described as a “macro.” People sometimes mean any automated or repeated action by that word. If the requirement is only “show numbers 1 to 100,” a formula or Fill Series is usually more straightforward and easier to share.

Common problems and fixes

If SEQUENCE returns #SPILL!, inspect the intended output area for existing values, merged cells, or another obstruction. Clear the blockage or move the formula to an area with enough room. If Excel displays #NAME?, check that the function name is spelled correctly and that the installed Excel version supports dynamic-array functions.

If the macro does not appear in the Macros list, check that the code is in a standard module, that the procedure is named correctly, and that the workbook is saved in .xlsm format. If Excel blocks macros, do not enable them automatically: first confirm that the workbook came from a trusted source. Organization security settings may also prevent macros from running.

If the VBA code fills the wrong cells, the active cell was not the intended starting point. The macro uses ActiveCell, so click the correct blank cell before running it. If values already exist below that cell, the code overwrites them. Keep a backup and verify the selected range before execution, since a macro’s edits cannot be undone with Ctrl+Z.

Finally, avoid using a generated sequence as a substitute for meaningful identifiers when records need permanent IDs. A formula-based sequence can change when rows are inserted, sorted, or recalculated, and a macro that writes values can still create duplicates if it is run more than once in the same location. For identifiers that must remain unique, design a deliberate record-numbering process.

Macro safety and workbook compatibility

VBA macros can change workbook contents, so treat them as executable code rather than as ordinary formulas. Run only macros you understand or that come from a source you trust. Keep an untouched copy of important data, particularly before testing a macro against a large range.

The example is deliberately limited: it writes numeric values into one column and does not delete sheets or change workbook settings. It still replaces cells in its output range. If adapting it to work across multiple columns, add explicit range checks and clear rules about whether existing data may be replaced. Save in .xlsm format to retain code; a standard .xlsx workbook does not store VBA macros.

Frequently Asked Questions

Can Excel automatically generate a number sequence without a macro?
Yes. In Excel 2021, Excel 2024, and Microsoft 365, enter a formula such as =SEQUENCE(10) in a blank cell to spill 1 through 10 down the sheet. For a one-time list, use Home > Editing > Fill > Series.

How do I create a sequence that starts at a number other than 1?
Use the start argument. With A1:A4 empty, enter =SEQUENCE(4,1,20,1) in A1 to return 20, 21, 22, and 23. The third argument is the starting number and the fourth is the increment.

Does the SEQUENCE function work in older Excel versions?
It requires a version with dynamic-array support, including Excel 2021 or Microsoft 365. In older Excel versions, use Home > Editing > Fill > Series, or use a VBA macro if automation is required.

Why does my Excel sequence show a spill error?
A spill error usually means Excel cannot place every result in the required cells. Clear occupied cells or merged cells in the output area, or move the formula to a larger empty area.

Can I run an Excel VBA sequence macro in a browser?
No. Excel for the web can store and open a macro-enabled workbook, but it cannot run VBA. Open the workbook in desktop Excel for Windows or Mac to execute the macro.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.