如何从集合范围对象中删除行

Question

一旦我在vba中设置了一个范围变量，有没有一种方法可以根据值从中删除行和/或列单元格？

例如：

Dim Lrange as Range
Set Lrange = Range("A1:E5")
For each row in Lrange
   If cell(Lrange.row, 3).value = "Somestring" Then
       LRange.row.delete
   End if
Next

这可能吗？还是我必须将其存储在工作表中才能操作范围？

欢迎提出任何建议！

Answer 1

如果您要删除，您应该向后循环通过行：

Dim Lrange                As Range
Dim n                     As Long
Set Lrange = Range("A1:E5")
For n = Lrange.Rows.Count To 1 Step -1
    If Lrange.Cells(n, 3).Value = "Somestring" Then
        Lrange.Rows(n).Delete
    End If
Next

例如。

Answer 2

如果您想根据一个值删除工作表中的行，那么下面的代码会执行此操作。。。

Sub DeleteRows()
Dim aRange As Range, aRow As Range, aCell As Range
    Set aRange = Range("A1:E5")
    For Each aRow In aRange.Rows
        For Each aCell In aRow.Cells
            If aCell.Value = "Somestring" Then
                aRow.EntireRow.Delete
                Exit For
            End If
        Next aCell
    Next aRow
End Sub

如果要根据值更改对象在VBA中引用的范围，则以下代码将执行此操作。。。

Sub DeleteRowsInObject()
Dim aRange As Range, aRow As Range, aCell As Range
Dim bRange As Range, found As Boolean
    Set aRange = Range("A1:E5")
    For Each aRow In aRange.Rows
        found = False
        For Each aCell In aRow.Cells
            If aCell.Value = "Somestring" Then found = True
        Next aCell
        If Not found Then
            If bRange Is Nothing Then
                Set bRange = aRow
            Else
                Set bRange = Union(bRange, aRow)
            End If
        End If
    Next aRow
    Set aRange = bRange
    Set bRange = Nothing
End Sub

对于与行/列不连续的Range对象，您需要小心，因为一些常规属性/方法不一定提供您期望的值。