删除行（向后工作），但是使用范围变量？ [英] Deleting rows (working backwards), but use a range variable?

问题描述

通常，您需要遍历一系列单元格，并根据某些条件删除整个行。

Often times it's required that you go through a range of cells, and based on some criteria, delete an entire row.

在实践中，最好从以下位置开始范围的 end ，然后进行计算。

In practice, it's best to start at the end of the range, and work up.

Dim i as Long
For i = lastRow to 1 Step -1
    If Cells(i, 2).Value = "del" then Rows(i).EntireRow.Delete
End if

但是，大多数时候我都在使用 Range 对象。

However, most of the time I'm working with a Range object.

有没有办法向后使用范围对象，不需要使用 For i 类型循环？

Is there a way to work backwards, with a range object, that doesn't require the use of a For i type loop?

Dim rng as Range, cel as Range
Set rng = Range("A1:A100")

For each cel in rng step -1
   if cel.value = "del" then cel.EntireRow.Delete
next cel

此错误预期：在步骤-1 部分的语句结尾，这是我所期望的（无双关语）。

This errors Expected: End of Statement on the Step -1 portion, which I expected (no pun intended).

想法是，在尝试时，我基本上不必将数据重新排列到 Cells（）中在 Range 变量上向后工作。我发现一堆使用范围变量有点麻烦，但是当要从该范围中删除行时，必须切换为使用 Cells（[long]，[long]）

The idea is that I don't have to basically re-arrange my data into Cells() when trying to work backwards on a Range variable. I find it a little kludgy to use range variables a bunch, but when wanting to remove rows from that range, have to switch to using Cells([long],[long]) if that makes sense.

编辑：刚想到了这个，但仍然感觉很笨拙：

Just came up with this, but it still feels kludgy:

Dim k As Long, cel as Range
Set cel = rng.cells(rng.cells.count)
For k = cel.Row To rng.Cells(1).Row Step -1
    If rng.Cells(k).Value = "del" Then rng.Cells(k).EntireRow.Delete
Next k

推荐答案

我知道您说过您不喜欢我，但是恕我直言，这是最干净的方法

I know you said you don't like For i, but IMHO this is the cleanest way to go

For i = rng.Rows.Count To 1 Step -1
    With rng.Cells(i, 2)
        If .Value = "del" then
            .Entirerow.Delete
        End If
    End With
Next

请注意， rng.Cells 构造为相对于 rng

Note that the rng.Cells construct is relative to rng

例如，如果 r ng 是A100：A1000，然后 rng.Cells（rng.Rows.Count，1）指的是A1000

Eg if rng is A100:A1000 then rng.Cells(rng.Rows.Count, 1) refers to A1000

这篇关于删除行（向后工作），但是使用范围变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！