How to insert numbers or rows for missing sequential number in Excel?

Inserting the number for the missing sequential number in Excel means that the user wants to complete the missing number in Excel by using the available Excel feature. This article will understand two basic ways to perform this task. The first example, guide the proper way to use VBA code to complete the missing sequence numbers in Excel. This example can be a little tedious as here, users need to learn the way to code and use proper code indentation to solve the task. Another example available in the sheet describes the method to use kutools, to fill up the missing number, by using the proper available options. Both the provided examples are detailed and brief and contain stepwise explanations to solve the data.

Example 1: To Insert the missing sequential numbers in Excel by using the VBA code.

Step 1

To understand the process of inserting numbers or rows for missing sequential data, the user needs to first create some sample data. As for this example, will be writing some random values like 101, 105, and 110. Consider below provided a snapshot of data for reference

Step 2

After that as described earlier, will be solving this example by using the VBA code, so the user first needs to open a VBA editor. To do so, use right−click with the sheet tab, and select the “View Code” option. For reference consider the below provided snapshot

Step 3

The above step will open a Microsoft Visual Basic Application dialog box. This dialog box contains a blank code area by default. A snapshot of the same is provided below

Step 4

Consider the below−given code, and copy the same to the editor:

' define function header
Sub insert_missing_value()
' start function body
' declare required variables
Dim range_wrk As Range
Dim r_range As Range
Dim arr_out As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'Set data for x title
xTitleId = "Macro Dilaog box"
' set range work
Set range_wrk = Application.Selection
Set range_wrk = Application.InputBox("Range", xTitleId, range_wrk.Address, Type:=8)
' assign value to first number
num1 = range_wrk.Range("A1").Value
' assign value to second number
num2 = range_wrk.Range("A" & range_wrk.Rows.Count).Value
' calculate the interval
interval = num2 - num1
ReDim arr_out(1 To interval + 1, 1 To 2)
' for each loop expression
For Each r_range In range_wrk
    ' set value to provided variables
    dic(r_range.Value) = r_range.Offset(0, 1).Value
' next statement
Next
' for each expression
For i = 0 To interval
    ' assign value
    arr_out(i + 1, 1) = i + num1
    ' if expression block
    If dic.Exists(i + num1) Then
        ' assign value
        arr_out(i + 1, 2) = dic(i + num1)
    ' else expression block
    Else
        ' assign value
        arr_out(i + 1, 2) = ""
    ' end of if statement
    End If
Next
' use with keyword to specify range
With range_wrk.Range("A1").Resize(UBound(arr_out, 1), UBound(arr_out, 2))
    .Value = arr_out
    .Select
' end of with block
End With
' end of sub block
End Sub

Step 5

After typing or pasting the above code, click on the “Run” button. A snapshot for the same is provided below:

Step 6

The above step will display a “Macro Dialog box”. In the appeared dialog box, select the range values, as for this example, will be choosing cells from A2 to A4. A snapshot of the same is provided below:

Step 7

As soon as the user clicks on the “OK” button. the missing data will be automatically entered inside the A column as shown below

Example 2: To Insert the missing sequential numbers in Excel by using the Kutools.

Step 1

In this example, also will be using the same Excel spreadsheet. Attaching the snapshot again for proper reference

Step 2

After that click on the “Kutools” tab, click on the “Editing” tile, and then select the “Insert” tab, further select the “Find Missing Sequence Number…”. A snapshot for the same is provided below

Step 3

The above step will open a “Find Missing Sequence Number” dialog box. In the range label, select the range data, for example for the provided case the data is present in the A2 to A4 cell. After that in the “Handling missing sequence number”, select the first radio button, “Inserting missing sequence number”, and finally click on the “OK” button. A snapshot for the same is provided below:

Step 4

The above step will again show a dialog box, “Find Missing Sequence Number”. In the appeared dialog box, click on the “OK” button.

Step 5

The final complete sequence output snapshot is provided below

Conclusion

This article demonstrates two examples, the first example describes the method to use the VBA code, while the second example describes the method to use the Kutools. Although, both examples, at last generate the same set of inputs. All the guided steps are detailed and accurate. A beginner can also perform the same task by referring to the guided article steps.