VBA:搜索子字符串并删除整行 [英] VBA: Searching substring and deleting entire row

问题描述

我正在尝试删除所有在 P 列中包含"H"的字符串的行.该宏有效，但是，每次仅删除一半的必要行.这是因为代码中的 For 循环-删除一行时，下一行将具有与删除的行相同的 i 值，并被跳过下一个我.

I'm trying to delete all rows which have a string that contains "H" in the P column. The macro works, however, it only deletes half of the necessary rows each time. This is because of the For loop in the code--when a row is deleted, the next row will have the same i value as the deleted one, and is skipped by the Next i.

Dim LastRow As Long

'Finds last row
With ActiveSheet
    LastRow = .Cells(.Rows.count, "P").End(xlUp).Row
End With

'Iterates through rows in column B, and deletes the row if string contains "H"
For i = 4 To LastRow
    If InStr(1, Range("P" & i), "H") <> 0 Then Rows(i).EntireRow.Delete
Next i

'Message Box when tasks are completed
  MsgBox "Complete"

如果为了删除所有行而删除了一行，是否可以让 For 循环重复相同的 i 值?

Is there a way to have the For loop repeat the same i value if a row is deleted in order to get all of the rows?

推荐答案

执行此操作的标准方法是以相反的顺序进行迭代.

The standard way to do this is to iterate in reverse order.

Dim LastRow As Long

'Finds last row
With ActiveSheet
    LastRow = .Cells(.Rows.count, "P").End(xlUp).Row
End With

'Iterates in reverse through rows in column B, and deletes the row if string contains "H"
For i = LastRow To 4 Step -1 
    If InStr(1, Range("P" & i), "H") <> 0 Then Rows(i).EntireRow.Delete
Next i

'Message Box when tasks are completed
  MsgBox "Complete"

这篇关于VBA:搜索子字符串并删除整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！