如果单元格包含字符串X，则删除整个行

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!

我很欣赏有什么建议。提前感谢!

http://i39.tinypic.com/5ano1d.png

6 个解决方案

#1

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中点击home->(编辑)排序和过滤器->过滤器)2。过滤“网站”第三栏。标记“无”并删除它们4。清晰的过滤器

#2

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.context tures.com/archives/2010/06/21/wayfind -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.
在“查找和替换”对话框中，在“查找什么”框中键入“paper”。
Click Find All, to see a list of cells with "paper"
单击“查找所有”，查看带有“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.
选择列表中的一个项目，按Ctrl+A选择整个列表，选择工作表上的所有“paper”单元格。
On the Ribbon's Home tab, click Delete, and then click Delete Sheet Rows.
在Ribbon Home选项卡上，单击“删除”，然后单击“删除表行”。

#3

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.

在“Developer”选项卡中，转到“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.

lRow:输入当前文件的行数。

The number "5" in the "If" is for the fifth (E) column

“If”中的数字“5”表示第五(E)列

#4

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包含了如何筛选特定列的屏幕截图，那就更好了。

#5

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 UsedRange is correct or change the offset to 2 rows instead of 1 in the .Offset(1,0).

我还没有对它进行测试，它是来自内存的，因此可能需要进行一些调整，但是它应该在不经过数千行循环的情况下完成这项工作。您需要删除11-Jul，以便UsedRange是正确的，或者将偏移量更改为2行，而不是. offset(1,0)中的1行。

Generally, before I do .Delete I will run the macro with .Select instead 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来运行宏，我可以确保正确的范围将被删除，这可能值得检查以确保适当的范围被删除。

#6

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

#1