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

Question

我一直在尝试使用一些片段来说明如何在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

然后宏将删除所有行，第4行除外，它是

NA NA NA NA

Answer 1

选择：）

方式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

Answer 2

Sub DeleteNumeric()
    Dim i As Long
    Dim rCell As Range
    Dim rRow As Range
    Dim rRng As Range
    'identify the range to search
    Set rRng = Sheet1.Range("A1:D5")
    'loop backwards when deleting rows
    For i = rRng.Rows.Count To 1 Step -1
        'loop through all the cells in the row
        For Each rCell In rRng.Rows(i).Cells
            If IsNumeric(rCell.Value) Then
                'delete the row and go to the next one
                rCell.EntireRow.Delete
                Exit For
            End If
        Next rCell
    Next i
End Sub

Answer 3

Dim currentPos As Integer
currentPos = 1
Do While (currentPos < yourNumberofRow)
If (Range("A" & currentPos).Value.IsNumeric = True) Then
    Rows(currentPos & ":" & currentPos).Select
    Selection.Delete
End If
currentPos = currentPos +1
Loop

不尝试但易于理解的代码删除和IsNumeric测试。