Excel VBA - 如果单元格是整数，则删除整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11767277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - If cell is an integer, delete the entire row
提问by tracer
I have been trying to use some snippets on how to delete entire rows on Excel VBA, but I can't modify them to include the "IsNumber" verification.
我一直在尝试使用一些关于如何在 Excel VBA 上删除整行的片段，但我无法修改它们以包含“IsNumber”验证。
I need to be able to choose an active area, like:
我需要能够选择一个活动区域，例如：
Set r = ActiveSheet.Range("A1:C10")
And as it goes through row after row (and checking every cell of the area), delete the entire row if a there is a number on a cell.
并且当它逐行遍历（并检查该区域的每个单元格）时，如果单元格上有数字，则删除整行。
For example:
例如：
NA NA NA 21
NA 22 NA 44
00 NA NA NA
NA NA NA NA
55 NA NA NA
The macro would then delete all the rows, except for the 4th one which is
然后宏将删除所有行，除了第 4 行
NA NA NA NA
回答by Siddharth Rout
Take your pick :)
随你选择:)
WAY 1 (TRIED AND TESTED)
方式 1（经过试验和测试）
This uses SpecialCellsto identify the rows which has numbers.
这用于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
WAY 2 (TRIED AND TESTED)
方式 2（尝试和测试）
This uses Looping and Count()to check for numbers
这使用循环并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
Way 3 (TRIED AND TESTED)
方式 3（经过试验和测试）
This uses Auto Filters. I am assuming that row 1 has headers and there is no blank cell in your range.
这使用自动过滤器。我假设第 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
回答by Dick Kusleika
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
回答by Dekx
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
Not try but easy code to understand delete and IsNumeric test.
不是尝试而是简单的代码来理解删除和 IsNumeric 测试。
