VB Excel删除指定值在单元格中的行 [英] VB Excel Deleting Rows On A Specified Value Being In a Cell

问题描述

$ $ $ $ $ $ $ $
如果InStr（c.Value，VALUE）然后
c.EntireRow。删除（）
结束如果
下一个

这只会删除一些

解决方案

这里有两种常见的删除整行行的模式，它们基于条件。主要的想法是，您不能从集合中删除。这意味着删除不应该出现在 For Each 中，这在大多数编程语言中都是相当标准的，有些甚至可以抛出错误，以防止它。

选项1 ，使用整数来跟踪行，并使其从头到尾起作用。您需要向后退，因为它是避免跳过行的简单方法。有可能前进，您只需要在删除时不要递增。

  Sub DeleteRowsWithIntegerLoop（）
 
 Dim rng_delete As Range 
设置rng_delete =范围（A1：A1000）
 
 Dim int_start As Integer 
 int_start = rng_delete.Rows.Count 
 
 Dim i As Integer 
对于i = int_start到1 Step -1 
如果InStr（rng_delete.Cells（i），VALUE）> 0然后
 rng_delete.Cells（i）.EntireRow.Delete 
 End If 
 Next i 
 
 End Sub 
  / pre> 
 
 
 选项2 ，使用联合删除模式构建范围的单元格，然后在最后一步删除它们。
  Sub DeleteRowsWithUnionDelete（）
 
 
 Dim rng_cell As Range 
 Dim rng_delete As Range 
 
对于每个rng_cell In Range（A1：A1000）
如果InStr（rng_cell，VALUE）> ; 0然后
如果rng_delete不是，然后
设置rng_delete = rng_cell 
 Else 
设置rng_delete = Union（rng_delete，rng_cell）
结束If 
 End If 
下一个
 
 rng_delete.EntireRow.Delete 
 
 End Sub 
  
 代码注释 
 
 
 
对于选项2，有一个额外的条件是创建在第一个项目开始时，rng_delete   Union 不适用于 Nothing 引用，所以我们先检查一下，如果是，将设置为第一个项目。所有其他人都通过设置通过联盟行。
 
 
 
 首选项 
 
 
 
在两者之间进行选择时，我总是喜欢选项2，因为我更喜欢使用范围，而不是使用计数器迭代 Cells 。这有限制。第二个选项也适用于不连续的范围和所有其他各种奇怪的范围（例如在调用 SpecialCells ），当您不确定要处理哪些数据时，可以使其有价值。
 
 
 
 速度 
 
 
 
我不确定速度比较。如果 ScreenUpdating 并启用计算，则两者都可能很慢。第一个选项使 N-1 调用删除，而第二个选项执行一个选项。 删除是一项昂贵的操作。然而，选项2会使 N-1 调用 Union 和 Set 。我认为它比第一个更快（这似乎在这里），但是我没有配置它。
 
 
 
最后注释： InStr 返回一个整数，表示找到该值的位置。我总是喜欢在这里做出这个布尔covnersion，并且比较> 0 。
 
    For Each c In oSheet.Range("A1:A1000")
        If InStr(c.Value, "VALUE") Then
            c.EntireRow.Delete()
        End If
    Next
This will only delete a few of the rows within the specified range, what could the problem be?
解决方案
Here are two common patterns for deleting entire rows based on a condition.  The main idea is that you cannot delete from a collection while you iterate it.  This means that Delete should not appear in a For Each  This is fairly standard across most programming languages and some even throw an error to prevent it.

Option 1, use an integer to track the rows and have it work from the end to the beginning.  You need to go backwards because it is the easy way to avoid skipping rows.  It is possible to go forwards, you just need to account for not incrementing when you delete.
Sub DeleteRowsWithIntegerLoop()

    Dim rng_delete As Range
    Set rng_delete = Range("A1:A1000")

    Dim int_start As Integer
    int_start = rng_delete.Rows.Count

    Dim i As Integer
    For i = int_start To 1 Step -1
        If InStr(rng_delete.Cells(i), "VALUE") > 0 Then
            rng_delete.Cells(i).EntireRow.Delete
        End If
    Next i

End Sub
Option 2, use the Union-Delete pattern to build a range of cells and then delete them all in one step at the end.
Sub DeleteRowsWithUnionDelete()


    Dim rng_cell As Range
    Dim rng_delete As Range

    For Each rng_cell In Range("A1:A1000")
        If InStr(rng_cell, "VALUE") > 0 Then
            If rng_delete Is Nothing Then
                Set rng_delete = rng_cell
            Else
                Set rng_delete = Union(rng_delete, rng_cell)
            End If
        End If
    Next

    rng_delete.EntireRow.Delete

End Sub
Notes on the code

For Option 2, there is an extra conditional there to create rng_delete when it starts at the first item.  Union does not work with a Nothing reference, so we first check that and if so, Set to the first item.  All others come through and get Set by the Union line.

Preference

When choosing between the two, I always prefer Option 2 because I much prefer to work with Ranges in Excel instead of iterating through Cells with a counter.  There are limitations to this.  The second option also works for discontinuous Ranges and all other variety of weird Ranges (e.g. after a call to SpecialCells) which can make it valuable when you are not sure what data you will be dealing with.

Speed

I am not sure about a speed comparison.  Both can be slow if ScreenUpdating and calculations are enabled.  The first option makes N-1 calls to Delete whereas the second option does a single one.  Delete is an expensive operation.  Option 2 does however make N-1 calls to Union and Set.  I assume it's faster than the first one based on that (and it seems to be here), but I did not profile it.

Final note: InStr returns an integer indicating where the value was found.  I always like to make the boolean covnersion explicit here and compare to >0.

                        
这篇关于VB Excel删除指定值在单元格中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！