Excel VBA删除行，直到条件 [英] excel vba Delete rows until criteria

问题描述

我是Excel VBA的新手，确实可以使用一些帮助。我尝试在整个网络上搜索解决方案，但找不到类似的问题。

I am new to Excel VBA and could really use some help. I tried searching for a solution throughout the web but was unable to find a similar problem.

我正在尝试创建一个宏，该宏将帮助您根据某些条件删除行并继续删除行，直到满足另一个特定条件为止。

I am trying to create a macro that will help to delete Rows based on certain criteria and continue deleting rows until another certain criterion is met.

例如。在下表中，我想删除Col A = 1 AND Col C = 0的行，然后继续删除该行 UNTIL Col A = 1的行。和Col C <> 0

So for example. In the table below, I would like to delete Rows where Col A = 1 AND Col C = 0, and then continue deleting the rows below that row UNTIL Col A = 1 and Col C <> 0

  A  |  B  |  C
-----|-----|-----
 1   |  TC |  2 
-----|-----|-----
 2   |  TC |  1 
-----|-----|-----
 1   |  TC |  0 
-----|-----|-----
 2   |  TC |  2 
-----|-----|-----
 3   |  TC |  1 
-----|-----|-----
 1   |  TC |  2 
-----|-----|-----
 1   |  TC |  0
-----|-----|-----
 1   |  TC |  1
-----|-----|-----
 2   |  TC |  0
-----|-----|-----
 3   |  TC |  2

因此，宏的最终结果为：

So the end result of the macro would be:

  A  |  B  |  C
-----|-----|-----
 1   |  TC |  2 
-----|-----|-----
 2   |  TC |  1 
-----|-----|-----
 1   |  TC |  2 
-----|-----|-----
 1   |  TC |  1
-----|-----|-----
 2   |  TC |  0
-----|-----|-----
 3   |  TC |  2

理想情况下，我想通过删除行来再次循环此操作，其中Col A = 2和Col C = 0，然后删除该行下方的行，直到Col A = 2且Col C <> 0。

Ideally, I would like to Loop this again with Deleting Rows where Col A = 2 and Col C = 0 and deleting the rows below that row until Col A = 2 and Col C <> 0.

下面是我想到的宏。欢迎所有建议并渴望学习。

Below is the macro that I Came up with. Open to all suggestions and eager to learn.

Sub deleterow()

Range("C2").Select
Do Until ActiveCell.Offset(0, -2) = "1" And ActiveCell.Value <> "0"
If ActiveCell.Value = "0" And ActiveCell.Offset(0, -2) = "1" Then
Rows(ActiveCell.Row & ":" & Rows.Count).Delete
End If
Loop

End Sub

期待听到您的回音！

谢谢

推荐答案

因为符合条件，所以循环在第一行停止。因此，下面的代码从第一行循环到最后填充的行，以搜索条件。

Your loop stops on the first line, because the criteria is met. So the code below loops from first row to last filled row to search for the criterias.

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1) 'Set the first worksheet to work
Dim n As Long, i As Long
Dim rng As Range, new_rng As Range

lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'lastrow number
For n = 1 To 2 'Criteria is Col A = 1 To 2
    For i = 1 To lastrow 'Loop from 1 to last row
        If ws.Cells(i, 1) = n And ws.Cells(i, 3) = 0 Then
            If rng Is Nothing Then Set rng = ws.Range("A" & i) 'Set first Range so Union won't give an error
            Set new_rng = ws.Range("A" & i)
            Set rng = Union(rng, new_rng) 'create a non contiguous range to delete
        End If
    Next i
Next n

rng.EntireRow.Delete

请注意，如果工作表很大，则不是最佳方法。有提高代码性能的最佳方法。使用循环，因为这是OP尝试的方法。

Note that if the Sheet is large, it isn't the optimal method. There are best ways to improve the code performance. A loop was used, because it was the method tried by the OP.

另一种方法是过滤多条件并删除显示的行

Another way is to filter for multicriteria and delete the shown rows.

这篇关于Excel VBA删除行，直到条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！