跳到主要內容

如何在Excel中為缺少的序號插入數字或行?

假設您在工作表中有一個序列號列表,但是序列中有一些缺失的數字,現在您需要插入缺失的數字或空白行以確保序列完整(如以下屏幕截圖所示)。 您如何在Excel中快速解決此問題?

文檔插入缺失編號 1 -2 文檔插入缺失編號 2

使用“排序和刪除重複項”功能為序列插入丟失的數字

使用VBA代碼插入缺少的序列號

使用VBA代碼為缺少的序列插入空白行

插入丟失的數字或空白行以使用Kutools for Excel進行排序


箭頭藍色右氣泡 使用“排序和刪除重複項”功能為序列插入丟失的數字

可能是您可以找到丟失的數字一個接一個,然後將其插入,但是如果有數百個連續數字,則很難確定丟失的數字的位置。 在Excel中,我可以使用“排序和刪除重複項”功能來處理此任務。

1。 在序列列表的末尾,填寫從2005023001到2005023011的另一個序列號。請參見屏幕截圖:

文檔插入缺失編號 3

2。 然後選擇兩個序列號的範圍,然後單擊 數據 > 將A到Z排序,請參見屏幕截圖:

文檔插入缺失編號 4

3。 所選數據已按照以下屏幕截圖進行了排序:

文檔插入缺失編號 5

4。 然後,您需要通過單擊刪除重複項 數據 > 刪除重複項,然後彈出 刪除重複項 對話框,檢查 您要刪除重複項的名稱,請參見屏幕截圖:

文檔插入缺失編號 6 -2 文檔插入缺失編號 7

5。 然後點擊 OK,其中的重複項 A欄 已被刪除,並且序列表中缺少的數字已插入,請參見屏幕截圖:

文檔插入缺失編號 8


箭頭藍色右氣泡 使用VBA代碼插入缺少的序列號

如果您覺得上述方法有太多步驟,這裡也有VBA代碼可以幫助您解決此問題。 請執行以下操作:

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊 窗口。

VBA:為序列插入缺失的數字

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。 然後按 F5 鍵運行此代碼,然後會彈出一個提示框,請選擇要插入缺失數字的數據范圍(不要選擇標題範圍),請參見屏幕截圖:

文檔插入缺失編號 9

4。 然後點擊 OK,缺少的數字已插入到序列表中。 查看屏幕截圖:

文檔插入缺失編號 1 -2 文檔插入缺失編號 2

箭頭藍色右氣泡 使用VBA代碼為缺少的序列插入空白行

有時,您只需要找到丟失數字的位置,並在數據之間插入空白行,以便可以根據需要輸入信息。 當然,以下VBA代碼也可以幫助您解決此問題。

1. 按住 ALT + F11 鍵,它會打開一個 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊 窗口。

VBA:為缺少的序列插入空白行

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。 然後按 F5 鍵以運行此代碼,並顯示一個提示框,然後選擇要為缺少的序列插入空白行的數據范圍(不要選擇標題範圍),請參見屏幕截圖:

文檔插入缺失編號 9

4。 然後點擊 OK,已為缺少的序列列表插入空白行。 查看屏幕截圖:

文檔插入缺失編號 1 -2 文檔插入缺失編號 10

箭頭藍色右氣泡 插入丟失的數字或空白行以使用Kutools for Excel進行排序

在這裡,我將介紹一個方便的工具- Excel的Kutools,其 查找丟失的序列號 功能,您可以在現有數據序列之間快速插入缺少的序列號或空白行。

Excel的Kutools : 帶有300多個便捷的Excel加載項,可以在30天內免費試用

如果你已經安裝 Excel的Kutools,請執行以下操作:

1。 選擇要插入缺少的數字的數據序列。

2。 點擊 庫工具 > 插入 > 查找丟失的序列號,請參見屏幕截圖:

3。 在 查找丟失的序列號 對話框,檢查 插入缺失的序列號 插入丟失的數字或我遇到缺失的序列號時插入空白行 根據需要插入空白行。 看截圖:

文檔插入缺失編號 10

4。 然後點擊 OK 按鈕,並且缺失的序列號或空白行已插入到數據中,請參見屏幕截圖:

文檔插入缺失編號 10 2 文檔插入缺失編號 10 2 文檔插入缺失編號 10

立即下載和免費試用Excel的Kutools!


箭頭藍色右氣泡  演示:使用Kutools for Excel插入缺失的數字或空白行以進行排序

Excel的Kutools:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!

相關文章:

如何在Excel中識別丟失的數字序列?

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have used the code for "VBA: insert blank rows for missing sequence" as listed above and works great - but i need it to insert rows across the all columns it only adds rows to the first 2 columns of my selection - not my entire table.
This comment was minimized by the moderator on the site
Hello, Melanie,

To solve your problem, maybe the following code can help you: (Note: A indicates the column contains the missing sequence, please change it to your need.)
Sub InsertBlankRowsForMissingSequence()
    Dim i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If IsNumeric(Cells(i, "A").Value) And IsNumeric(Cells(i - 1, "A").Value) And Cells(i, "A").Value <> "" And Cells(i - 1, "A").Value <> "" Then
            If Cells(i, "A").Value - Cells(i - 1, "A").Value > 1 Then
                Debug.Print Cells(i, "A").Value - Cells(i - 1, "A").Value - 1
                Rows(i).Resize(Cells(i, "A").Value - Cells(i - 1, "A").Value - 1).Insert
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
I am trying to use the VBA for sequential numbers. I have several columns next to the numbers of which numbers too. I.e.
1. HL Meter 34
2. HL Watermeter 40
4. HL CO2meter 24

When I use the code it works for the first 3 columns but it gets mixed up if I include the 4th column since it includes numbers too.
How can I change the code to make sure the numbers in column 4 stay the same?
This comment was minimized by the moderator on the site
Thank you amazing
This comment was minimized by the moderator on the site
What if i want to select 6 columns and then check 1st column for dates and if dates are missing add a row(blank cells) for all 6 columns
This comment was minimized by the moderator on the site
I want to use "Inserting missing sequence Number" feature but it's not supporting for digits more than 12 ? there are many sets in which I want to insert the sequence between (it's a alpha-numeric digit) can you help
This comment was minimized by the moderator on the site
Hi, I want to use "Inserting Missing Sequence Number", but it's not supporting if the no. of digits are more than 12 can you help ?
This comment was minimized by the moderator on the site
What if i want to select 6 columns and then check 1st column for dates and if dates are missing add a row(blank cells) for all 6 columns
This comment was minimized by the moderator on the site
Thank you very much. How do i change the script if the increments is only 0.02 and not 1 This is for the script InsertNullBetween()
This comment was minimized by the moderator on the site
this worked and was very easy to complete the task. Thank you.
This comment was minimized by the moderator on the site
Thanks ! Great script ! How i can modify this script if i say we need to process not only ID column + NAME column, but ID column + NAME column + NEW column ? How i can add new columns in this script?
This comment was minimized by the moderator on the site
The following is the modified macro to include an added column - Another important point is that when prompted to select the range, you should only select the first column - these took me a few hours! hope to save others' time

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")
Dim dic2 As Variant
Set dic2 = 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 3)
For Each Rng In WorkRng
dic(Rng.Value) = Rng.Offset(0, 1).Value
dic2(Rng.Value) = Rng.Offset(0, 2).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)
outArr(i + 1, 3) = dic2(i + num1)
Else
outArr(i + 1, 2) = ""
outArr(i + 1, 3) = ""

End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
.Value = outArr
.Select
End With
End Sub
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations