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

Supposing you have a list of sequential numbers in a worksheet, but there are some missing numbers among the sequence, and now you need to insert the missing numbers or blank rows to make sure the sequence be complete (shown as the following screenshots). How could you quickly solve this problem in Excel?

#### **Insert missing numbers for sequence with Sort and Remove Duplicates feature **

May be you can find the missing numbers one by one, and then insert them, but it is difficult for you to identify the location of the missing ones if there are hundreds of sequential numbers. In Excel, I can use the Sort and Remove Duplicates feature to deal with this task.

**1**. Following the end of the sequence list, fill in another sequence numbers from 2005023001 to 2005023011. See screenshot:

**2**. Then select the range of the two sequence numbers and click **Data** > **Sort A to Z**, see screenshot:

**3**. And the selected data has been sorted as following screenshot:

**4**. Then you need to remove the duplicates by click** Data** > **Remove Duplicates**, and in the popped out **Remove Duplicates** dialog box, check the **Column** name that you want to remove the duplicates, see screenshots:

**5**. Then click** OK**, the duplicates in **Column A** has been deleted, and the missing numbers in the sequence list have been inserted, see screenshot:

#### **Insert missing numbers for sequence with VBA code **

If you feel there are so many steps with the above methods, here also have VBA code can help you finish this problem. Please do as follows:

**1**. Hold down the **ALT + F11** keys, and it opens the **Microsoft Visual Basic for Applications** window.

**2**. Click** Insert** > **Module**, and paste the following code in the **Module** window.

**VBA: insert missing numbers for the sequence**

```
Sub InsertValueBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
outArr(i + 1, 1) = i + num1
If dic.Exists(i + num1) Then
outArr(i + 1, 2) = dic(i + num1)
Else
outArr(i + 1, 2) = ""
End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
.Value = outArr
.Select
End With
End Sub
```

**3**. Then press **F5** key to run this code, and a prompt box will pop out, please select the data range that you want to insert the missing numbers(don’t select the title range), see screenshot:

**4**. And then click **OK**, the missing numbers have been inserted to the sequence list. See screenshots:

#### **Insert blank rows for missing sequence with VBA code **

Sometimes, you just need to locate the place of the missing numbers and insert blank rows between the data, so that you can enter the information as you need. Of course, the following VBA code also can help you solve this problem.

**1.** Hold down the **ALT + F11** keys, and it opens a **Microsoft Visual Basic for Applications** window.

**2**. Click **Insert** > **Module**, and paste the following code in the **Module** window.

**VBA: insert blank rows for missing sequence**

```
Sub InsertNullBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
If dic.Exists(i + num1) Then
outArr(i + 1, 1) = i + num1
outArr(i + 1, 2) = dic(i + num1)
Else
outArr(i + 1, 1) = ""
outArr(i + 1, 2) = ""
End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
.Value = outArr
.Select
End With
End Sub
```

**3**. Then press **F5** key to run this code, and a prompt box will display, and then select the data range that you want to insert blank rows for the missing sequence(don’t select the title range), see screenshot:

**4**. And then click **OK**, the blank rows have been inserted for the missing sequence list. See screenshots:

#### **Insert missing numbers or blank rows for sequence with Kutools for Excel **

Here, I will introduce an easy and handy tool- **Kutools for Excel**, with its **Find Missing Sequence Number** feature, you can quickly insert the missing sequence number or blank rows between the existing data sequence.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |

If you have installed** Kutools for Excel**, please do as follows:

**1**. Select the data sequence that you want to insert the missing numbers.

**2**. Click** Kutools** > **Insert** > **Find Missing Sequence Number**, see screenshot:

**3**. In the **Find Missing Sequence Number** dialog box, check **Inserting missing sequence number** to insert the missing numbers or I**nserting blank rows when encountering missing sequence numbers** to insert blank rows as you need. See screenshot:

**4**. And then click **OK** button, and the missing sequence numbers or blank rows have been inserted into the data , see screenshots:

**Download and free trial Kutools for Excel Now ! **

** Demo: Insert missing numbers or blank rows for sequence with Kutools for Excel**

**Kutools for Excel**: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

**Download and free trial Now!**

**Related article:**

How to identify missing numbers sequence in Excel?

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!