跳到主要内容

如何在Excel中为缺少的序号插入数字或行?

作者:晓阳 最后修改时间:2020-05-27

假设您在工作表中有一个序列号列表,但是序列中有一些缺失的数字,现在您需要插入缺失的数字或空白行以确保序列完整(如以下屏幕截图所示)。 您如何在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应用程序 窗口。

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应用程序 窗口。

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 插入缺失的数字或空白行以进行序列

在这里,我将介绍一个简单易用的工具- Kutools for Excel,其 查找丢失的序列号 功能,您可以在现有数据序列之间快速插入缺少的序列号或空白行。

Kutools for Excel : 带有300多个便捷的Excel加载项,可以在30天内免费试用

如果你已经安装 Kutools for Excel,请执行以下操作:

1。 选择要插入缺少的数字的数据序列。

2。 点击 库工具 > 插页 > 查找丢失的序列号,请参见屏幕截图:

3。 在 查找丢失的序列号 对话框,检查 插入缺失的序列号 插入丢失的数字或我遇到缺失的序列号时插入空白行 根据需要插入空白行。 看截图:

文档插入缺失编号 10

4。 然后点击 OK 按钮,并且缺失的序列号或空白行已插入到数据中,请参见屏幕截图:

文档插入缺失编号 10 2 文档插入缺失编号 10 2 文档插入缺失编号 10

立即下载和免费试用Excel的Kutools!


箭头蓝色右气泡  演示:使用 Kutools for Excel 插入缺失的数字或空白行以进行序列

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!

相关文章:

如何在Excel中识别缺失的数字序列?

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 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
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations