vba - 如何删除所有包含非数值的行

Question

我有一张看起来像这样的表格，上面有填充的列..

                       Column B     Column C     Column D
row 2 : Product 1      100          200          300
row 3 : Product 2      100          200A         100
row 4 : Product 3      AAA          200          300
row 5 : Product 4      600          200          300
row 6 : Product 5      150          200          300,2A

我想要的是:
对于每一行，如果 columnB 的值不是数字或 columnC 的值不是数字或 columnD 的值不是数字，则删除该行。

这是我的代码，对我来说看起来不错:

Dim xWs As Worksheet
Set xWS = ThisWorkbook.Sheets("sheet1")
lastrow = xWs.Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lastrow
    BValue = xWs.Range("B" & i).Value
    CValue = xWs.Range("C" & i).Value
    DValue = xWs.Range("D" & i).Value
    If Not IsNumeric(BValue) Or Not IsNumeric(CValue) Or Not IsNumeric(DValue) Then
         xWs.Rows(i).Delete
    End If
Next i

我的问题是:当我执行代码时..它删除了第 4 行和第 6 行，但保留了第 3 行，尽管 columnB 值不是数字..

我无法弄清楚问题..
提前致谢 ..

Answer 1

您可以一次性使用 Union 和 delete 以避免通过向上删除行来跳过行。您应该向后删除行。

Option Explicit

Sub test()

Dim xWs As Worksheet, i As Long, lastRow As Long, unionRng As Range
Set xWs = ThisWorkbook.Sheets("sheet1")
lastRow = xWs.Cells(xWs.Rows.Count, 2).End(xlUp).Row
Dim BValue As Variant, CValue As Variant, DValue As Variant

For i = 2 To lastRow
    BValue = xWs.Range("B" & i).Value
    CValue = xWs.Range("C" & i).Value
    DValue = xWs.Range("D" & i).Value
    If Not IsNumeric(BValue) Or Not IsNumeric(CValue) Or Not IsNumeric(DValue) Then
        If Not unionRng Is Nothing Then
            Set unionRng = Union(unionRng, xWs.Rows(i))
        Else
            Set unionRng = xWs.Rows(i)
        End If

    End If
Next i

If Not unionRng Is Nothing Then unionRng.Delete

End Sub

或者倒退:

Option Explicit

Sub test()

Dim xWs As Worksheet, i As Long, lastRow As Long, unionRng As Range
Set xWs = ThisWorkbook.Sheets("sheet1")
lastRow = xWs.Cells(xWs.Rows.Count, 2).End(xlUp).Row
Dim BValue As Variant, CValue As Variant, DValue As Variant

For i = lastRow To 2 Step -1
    BValue = xWs.Range("B" & i).Value
    CValue = xWs.Range("C" & i).Value
    DValue = xWs.Range("D" & i).Value
    If Not IsNumeric(BValue) Or Not IsNumeric(CValue) Or Not IsNumeric(DValue) Then xWs.Rows(i).Delete
Next i

End Sub