根据搜索键 VBA 删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13614997/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-11 18:37:42 来源:igfitidea点击:
Delete Row based on Search Key VBA
提问by Anthony
I am trying to delete everyrow in which the value "X" is found in column B using VBA. However, I'm having three problems:
我正在尝试使用 VBA删除在 B 列中找到值“X”的每一行。但是，我遇到了三个问题：
- I can't get my VBA code to move down from the active cell to the next cell (B3) using cells.find method (see code below)
- My code does not delete the entire row where the value "X" is found in column B
- The amount of Data in Column B can vary: it can end at B10 today, or B100 tomorrow (see screen shot below)
- 我无法使用 cell.find 方法让我的 VBA 代码从活动单元格向下移动到下一个单元格（B3）（请参阅下面的代码）
- 我的代码不会删除在 B 列中找到值“X”的整行
- B 列中的数据量可能会有所不同：它可能在今天结束于 B10，或明天结束于 B100（见下面的屏幕截图）
Any assistance will be greatly appreciated.
任何帮助将不胜感激。
Sub RemoveRows()
Dim strLookFor As String
Dim strRow As String
Worksheets("Sheet1").Range("B2").Activate
strLookFor = "X"
strRow = Range("B2").Address
Do Until ActiveCell.Value = ""
MsgBox (ActiveCell.Value)
If ActiveCell.Value = strLookFor Then
Rows.Delete (strRow)
End If
strRow = Cells.Find(what:=strLookFor).Address
Loop
MsgBox ("Deleted all rows with value " & strLookFor)
End Sub
回答by brettdj
Using an AutoFilteris much more efficient than a range loop
使用 anAutoFilter比范围循环更有效
Sub QuickCull()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Sheet1")
Set rng1 = ws.Range(ws.[b2], ws.Cells(Rows.Count, "B").End(xlUp))
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
rng1.AutoFilter Field:=1, Criteria1:="X"
rng1.Offset(1, 0).EntireRow.Delete
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
回答by Tim Williams
dim x as long
dim r as long
dim sht as worksheet
set sht = Worksheets("Sheet1")
r = sht.Cells(rows.count,2).end(xlup).row
for x = r to 2 step -1
with sht.cells(x,2)
if .value = "X" then .entirerow.delete
end with
next x
回答by David
This should work:
这应该有效：
Sub DeleteRowsWithX()
maxRow = ActiveSheet.UsedRange.Rows.Count
For i = 1 To maxRow
Do While (StrComp(ActiveSheet.Cells(i, 2).Text, "X", vbTextCompare) = 0)
Rows(i).Select
Selection.Delete Shift:=xlUp
Loop
Next
End Sub
