I’m trying to find a way to get numbers within a range. I have the minimum and maximum of the range and I want to insert the numbers in between the ranges. Instead of entering the numbers manually, is there a way I can automate this?

Example and outcome pictures attached:

I’m trying to find a way to get numbers within a range. I have the minimum and maximum of the range and I want to insert the numbers in between the ranges. Instead of entering the numbers manually, is there a way I can automate this?

Example and outcome pictures attached:

• excel
• vba

2 Answers 2

Pls try

Option Explicit

Sub Demo()
    Const OUTPUT_CELL = "D1" ' Start cell of output
    Dim i As Long, j As Long
    Dim arrData, rngData As Range
    Dim arrRes, iR As Long
    Dim LastRow As Long
    Dim oSht1 As Worksheet: Set oSht1 = Sheets("Sheet1")
    Set rngData = oSht1.Range("A1").CurrentRegion
    ' load table into an array
    arrData = rngData.Value
    With Application
        ReDim arrRes(.Max(rngData) - .Min(rngData) + 1, 0)
    End With
    ' header of output
    arrRes(0, 0) = "Output"
    ' populate output array
    For i = LBound(arrData) + 1 To UBound(arrData)
        For j = arrData(i, 1) To arrData(i, 2)
            iR = iR + 1
            arrRes(iR, 0) = j
        Next j
    Next i
    ' write output to sheets
    oSht1.Range(OUTPUT_CELL).Resize(iR + 1, 1).Value = arrRes
End Sub

Microsoft documentation:

Range.Resize property (Excel)

Range.CurrentRegion property (Excel)

Do you have the Sequence() worksheet function on your computer? This gives you the possibility to do this without VBA, using the formula =Sequence(C2-B2+1;1;B2;1):

Explanation of the parameters:

• C2-B2+1 : the amount of entries (rows) you need is 9 (=10-2+1).
• 1 : everything in one column.
• B2 : you start by the value in "B2".
• 1 : you increase by one.