时间戳

首页 > 解决方案 > 循环遍历行时删除行

vba - 循环遍历行时删除行

问题描述

我正在尝试从 a 中删除所有Range具有值的单元格的行"Totals"。我试过这样的事情：

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

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

标签： vbaexcel

解决方案

你需要这样的模式：

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

推荐阅读