Excel VBA如果单元格包含文本则删除行 [英] Excel vba Deleting a row if a cell contains a text

问题描述

我正在使用以下代码:

Dim r As Integer   
For r = 1 To Sheet1.UsedRange.Rows.Count        
    If Cells(r, "Q") = "0" Then           
    Sheet1.Rows(r).EntireRow.Delete    
    End If
Next  

问题是它不会删除所有内容，它只会删除某些行，而且我必须多次按下使用此代码的按钮才能删除"Q"中所有"0"实例

The problem is it doesn't delete everything, it just deletes some of the rows and i have to press the button that uses this code multiple times to delete all instances of "0" in "Q"

类似问题的答案

如何反转For循环
基于搜索键VBA删除行

推荐答案

我已经在注释中解决了这个问题，但是为了完整起见，您需要反转for/next循环的顺序，如下所示:

I appreciate this has been addressed in the comments, but for completeness, you need to reverse the order of the for/next loop as shown below:

Dim r As Integer   
For r = Sheet1.UsedRange.Rows.Count to 1 step -1
    If Cells(r, "Q") = "0" Then           
        Sheet1.Rows(r).EntireRow.Delete    
    End If
Next  

您以前的操作方式是，通过删除列表开头的整行，将所有内容向上移动了一行.通过从下而上开始，删除整行不会影响您尚未处理的项目的行号.

The way you had it previously, by deleting an entire row at the start of the list, you were moving all the content up one row. By starting form the bottom up, deleting an entire row doesn't impact the rows numbers for the items you haven't processed yet.

很高兴您对它进行了排序.

Glad you got it sorted.

这篇关于Excel VBA如果单元格包含文本则删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！