vba - 如何在满足条件时删除Excel中的整行

Question

我正在开发的程序涉及读取并确定 Excel 表格中的 i - (i+1) 和 i-(i-1) 之间的差异。如果差值超过 4，则程序删除 i 处的行。该程序第一次尝试效果很好。但是，当我再次重复它时，它开始删除不应删除的点。

选项显式

子数据_Delet()

Dim a As Double, b As Double, c As Double, i As Double
Dim rkill As Range
' a,b, and c are used as steps in order to proceed to the next data points
a = 18
b = 0
c = 0

With ThisWorkbook.Worksheets("Sheet1")

    ' The second do loop delete data points that does not follow the requirements
    Do
    If Abs(.Cells(a - 1, 2) - .Cells(a, 2)) > 4 And Abs(.Cells(a, 2) - .Cells(a + 1, 2)) > 4 Then
         If rkill Is Nothing Then
            Set rkill = Rows(a)
         Else
            Set rkill = Union(rkill, Rows(a))
         End If
    End If
    a = a + 1
    Loop Until .Cells(a, 2).Value = ""
    rkill.EntireRow.Delete
End With

结束子

Answer 1

改变

rkill.EntireRow.Delete

至

If Not rkill is Nothing Then rkill.EntireRow.Delete

此代码检查范围rkill是否为范围，然后将其删除。如果不是范围则不会执行后面的部分。

另一种选择是(我不建议)

On Error Resume Next
rkill.EntireRow.Delete
On Error GoTo 0