vba 如果单元格包含字符串 X 则删除整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17606045/
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
Delete entire row if cell contains the string X
提问by AnchovyLegend
I am new to VBA and I am trying to come up with a way to delete all rows (and shift cells up, if possible) where the website column cell contains the word none. The table contains 5000+ records and this would save me a great amount of time.
我是 VBA 的新手，我试图想出一种方法来删除网站列单元格包含单词none. 该表包含 5000 多条记录，这将为我节省大量时间。
I appreciate any suggestions. Many thanks in advance!
我很感激任何建议。提前谢谢了！
回答by MBK
This is not a VBA task - This specific task is easiest sollowed with Auto filter.
这不是 VBA 任务 - 使用自动过滤器最容易解决此特定任务。
1.Insert Auto filter (In Excel 2010 click on home-> (Editing) Sort & Filter -> Filter)
2. Filter on the 'Websites' column
3. Mark the 'none' and delete them
4. Clear filter
1.插入自动过滤器（在Excel 2010中点击主页->（编辑）排序和过滤器->过滤器）
2.过滤“网站”列
3.标记“无”并删除它们
4.清除过滤器
回答by Barry Carter
Ok I know this for VBA but if you need to do this for a once off bulk delete you can use the following Excel functionality: http://blog.contextures.com/archives/2010/06/21/fast-way-to-find-and-delete-excel-rows/Hope this helps anyone
好的，我知道这适用于 VBA，但如果您需要一次性批量删除，您可以使用以下 Excel 功能：http: //blog.contextures.com/archives/2010/06/21/fast-way-to -find-and-delete-excel-rows/希望这对任何人都有帮助
Example looking for the string "paper":
查找字符串“paper”的示例：
- In the Find and Replace dialog box, type "paper" in the Find What box.
- Click Find All, to see a list of cells with "paper"
- Select an item in the list, and press Ctrl+A, to select the entire list, and to select all the "paper" cells on the worksheet.
- On the Ribbon's Home tab, click Delete, and then click Delete Sheet Rows.
- 在“查找和替换”对话框中，在“查找内容”框中键入“纸张”。
- 单击“查找全部”，查看带有“纸”的单元格列表
- 选择列表中的一个项目，然后按 Ctrl+A，选择整个列表，并选择工作表上的所有“纸”单元格。
- 在功能区的主页选项卡上，单击删除，然后单击删除工作表行。
回答by Apolo Radomer
In the "Developer Tab" go to "Visual Basic" and create a Module. Copy paste the following. Remember changing the code, depending on what you want. Then run the module.
在“开发人员选项卡”中，转到“Visual Basic”并创建一个模块。复制粘贴以下内容。记住更改代码，这取决于您想要什么。然后运行模块。
Sub sbDelete_Rows_IF_Cell_Contains_String_Text_Value()
Dim lRow As Long
Dim iCntr As Long
lRow = 390
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 5).Value = "none" Then
Rows(iCntr).Delete
End If
Next
End Sub
lRow : Put the number of the rows that the current file has.
The number "5" in the "If" is for the fifth (E) column
lRow : 放入当前文件的行数。
"If" 中的数字 "5" 用于第五 (E) 列
回答by palmbardier
I'd like to add to @MBK's answer. Although I found @MBK's answer to be very helpful in solving a similar problem, it'd be better if @MBK included a screenshot of how to filter a particular column.
我想补充@MBK的答案。虽然我发现@MBK 的回答对解决类似问题非常有帮助，但如果@MBK 包含如何过滤特定列的屏幕截图会更好。
回答by Soulfire
This was alluded to in another comment, but you could try something like this.
这是在另一条评论中提到的，但你可以尝试这样的事情。
Sub FilterAndDelete()
Application.DisplayAlerts = False
With Sheet1 'Change this to your sheet name
.AutoFilterMode = False
.Range("A3:K3").AutoFilter
.Range("A3:K3").AutoFilter Field:=5, Criteria1:="none"
.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
End With
Application.DisplayAlerts = True
End Sub
I haven't tested this and it is from memory, so it may require some tweaking but it should get the job done without looping through thousands of rows. You'll need to remove the 11-Jul so that UsedRangeis correct or change the offset to 2 rows instead of 1 in the .Offset(1,0).
我还没有测试过这个，它是从内存中获取的，所以它可能需要一些调整，但它应该可以在不循环数千行的情况下完成工作。您需要删除 11-Jul 以便UsedRange正确或将偏移量更改为 2 行而不是.Offset(1,0).
Generally, before I do .DeleteI will run the macro with .Selectinstead of the Delete that way I can be sure the correct range will be deleted, that may be worth doing to check to ensure the appropriate range is being deleted.
通常，在我这样做之前，.Delete我将使用.Select而不是 Delete 来运行宏，这样我可以确定将删除正确的范围，这可能值得检查以确保删除了适当的范围。
回答by matzone
Try this ...
尝试这个 ...
Dim r as Range
Dim x as Integer
For x = 5000 to 4 step -1 '---> or change as you want //Thanx 4 KazJaw
set r = range("E" & format(x))
if ucase(r.Value) = "NONE" then
Rows(x).EntireRow.Delete
end if
Next
回答by Michael
- Delete rows 1 and 2 so that your headings are on row 1
Put this in a macro (IT WILL CHECK THROUGH ROW 75000, YOU CAN LOWER THE NUMBER IF YOU WOULD LIKE
Columns("E:E").Select Selection.AutoFilter ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1, Criteria1:="none" Range("E2:E75000").SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete ActiveSheet.Cells.EntireRow.Hidden = False ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1 Columns("E:E").Select Selection.AutoFilter Range("E2").Select Range("A1").Select
- 删除第 1 行和第 2 行，使您的标题位于第 1 行
把它放在一个宏中（它会检查 75000 行，如果你愿意，你可以降低这个数字
Columns("E:E").Select Selection.AutoFilter ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1, Criteria1:="none" Range("E2:E75000").SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete ActiveSheet.Cells.EntireRow.Hidden = False ActiveSheet.Range("$E$1:$E$75000").AutoFilter Field:=1 Columns("E:E").Select Selection.AutoFilter Range("E2").Select Range("A1").Select
