Excel VBA - 如果单元格是整数，则删除整行

我一直在尝试使用一些关于如何删除 Excel VBA 上的整行的代码片段，但我无法修改它们以包含“IsNumber”验证。

我需要能够选择一个活动区域，例如：

Set r = ActiveSheet.Range("A1:C10")

当它逐行浏览时（并检查该区域的每个单元格），如果单元格上有数字，则删除整行。

例如：

NA NA NA 21
NA 22 NA 44
00 NA NA NA
NA NA NA NA
55 NA NA NA

然后，宏将删除所有行，除了第四行之外

NA NA NA NA

任君选择:)

方式 1（经过尝试和测试）

这使用SpecialCells识别有数字的行。

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    On Error GoTo Whoa

    Set ws = Sheets("Sheet1")

    With ws
        Set rng = .Cells.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow

        rng.ClearContents '<~~ or rng.Clear if cells have formatting

        .Cells.Sort Key1:=.Range("A1")
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

方式 2（经过尝试和测试）

这使用循环和Count()检查号码

Sub Sample()
    Dim ws As Worksheet
    Dim delrange As Range
    Dim lRow As Long, i As Long

    On Error GoTo Whoa

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            If Application.WorksheetFunction.Count(.Rows(i)) > 0 Then
                If delrange Is Nothing Then
                    Set delrange = .Rows(i)
                Else
                    Set delrange = Union(delrange, .Rows(i))
                End If
            End If
        Next i

        If Not delrange Is Nothing Then delrange.Delete
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

方式 3（经过尝试和测试）

这使用自动过滤器。我假设第 1 行有标题，并且您的范围内没有空白单元格。

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long, i As Long
    Dim ColN As String

    On Error GoTo Whoa

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For i = 1 To lCol
            '~~> Remove any filters
            .AutoFilterMode = False
            ColN = Split(.Cells(, i).Address, "$")(1)

            '~~> Filter, offset(to exclude headers) and delete visible rows
            With .Range(ColN & "1:" & ColN & lRow)

                .AutoFilter Field:=1, Criteria1:=">=" & _
                Application.WorksheetFunction.Min(ws.Columns(i)), _
                Operator:=xlOr, Criteria2:="<=" & _
                Application.WorksheetFunction.Max(ws.Columns(i))

                .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End With

            '~~> Remove any filters
            .AutoFilterMode = False
        Next
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub