vba - 选择一系列单元格，并为这些单元格序列偏离的每个数字插入一个空白行

Question

我有一列包含超过 19,000 行。我想要做的是运行一个 vba 代码，该代码将选择该列中的一系列单元格，并为所选范围内序列中缺少的每个数字添加一个空白行。目前，我正在使用的代码将允许我选择一系列单元格，但是在选择所述范围后，它会给我行 gap = Right(.Cells(i), 5) 的类型不匹配错误- 右(.Cells(i - 1), 5) 。如果我获取单元格范围并将它们复制到新工作表中，则代码将完全按照我想要的方式执行。当我在包含超过 19000 个单元格的列上运行它时，您知道为什么会出现不匹配错误吗？

我正在使用的代码是:

Option Explicit

Sub InsertNullBetween()
Dim i As Long, gap As Long
'Update 20130829
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
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", WorkRng.Address, Type:=8)
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
    For i = .Rows.Count To 2 Step -1
        gap = Right(.Cells(i), 5) - Right(.Cells(i - 1), 5)
        If gap > 1 Then .Cells(i).Resize(gap - 1).Insert xlDown 
    Next
End With
End Sub

Answer 1

要更详细地开发我在评论中的答案，并将您的代码重构到所需的最低限度:

Option Explicit

Sub InsertNullBetween()
    Dim i As Long, gap As Long
    Dim WorkRng As Range

    On Error Resume Next
    Set WorkRng = Application.InputBox(Prompt:="Range To Check", Title:="Select a Range", Default:=Selection.address, Type:=8)
    On Error GoTo 0
    If WorkRng Is Nothing Then Exit Sub '<--| check user hasn't canceled the dialog box
    With WorkRng
        For i = .Rows.count To 2 Step -1
            gap = Right(.Cells(i), 5) - Right(.Cells(i - 1), 5)
            If gap > 1 Then .Cells(i).Resize(gap - 1).Insert xlDown
        Next
    End With
End Sub