在循环访问行时删除行

Question

我正在尝试删除Range中的所有行，这些行中有一个值为"Totals"的单元格。我尝试了这样的事情：

For Each cell In rng
If CStr(cell.Value) = "Totals" Then
cell.EntireRow.Delete
End If
Next cell

问题是，每当有两行都包含一个单元格时"Totals"只有第一行被删除。我该如何解决这个问题？

Answer 1

你需要这样的模式：

Dim numberOfRows as Integer, rw as Integer, col as Integer
numberOfRows = 100  // You probably want to set this using your `rng` object 
col = 1 // column 'A' (but set for your situation)
For rw = numberOfRows to 1 Step -1
If CStr(Cells(rw, col)) = "Totals" Then
Cells(rw, col).EntireRow.Delete
End If
Next rw

编辑两种替代方法

假设我在A1:C3中有如下数据：

A    B        C
1    1    2        3
2    4    Totals   5
3    6    7        8

我想删除任何包含Totals的行。有两种方法可以做到这一点：

Sub MarkToDelete()
Dim rng As Range, cl As Range, rw As Integer
Set rng = Range("A1:C3")
For Each cl In rng
If cl = "Totals" Then
Cells(cl.Row, 4) = "DELETE" //i.e. in column D add a note to delete
End If
Next cl
For rw = rng.Rows.Count To 1 Step -1
If Cells(rw, 4) = "DELETE" Then
Cells(rw, 4).EntireRow.Delete
End If
Next rw
End Sub
Sub LoopRemove()
Dim rw As Integer, col As Integer
Set rng = Range("A1:C3")
For rw = rng.Rows.Count To 1 Step -1
For col = 1 To rng.Columns.Count
If Cells(rw, col) = "Totals" Then
Cells(rw, col).EntireRow.Delete
End If
Next col
Next rw
End Sub