删除行而不跳过一行如何？ [英] Removing rows without skipping a row how to?

问题描述

下面是我的代码。我试图通过一些行，如果有某些数据，然后删除这些行，但是我现在编写它的方式，每当我删除一行，我跳过它下面的一个。我改变了范围走高编号行到低数行，但我的宏仍然开始在顶部，并下移。我想如果我把它移动了，删除不会导致它跳过下一个项目。我怎样才能使它从列表的底部向上移动，或者有什么更好的方法来做到这一点？
我的代码如下：

  Dim lLastRow As Long 
 Dim num As Integer 
 Dim name As String 
 Dim rCell As Range 
 Dim afCell As Range 
 Dim rRng As Range 
 Dim affectedRng As Range 
 
 Windows（受影响最大的客户信息（1，1）。激活
工作表（Sheet 1）。 .Row 
 
 Set affectedRng =范围（A& lLastRow&：A2）
'首先删除已解析的条目
对于每个afCell在affectedRng 
如果（afCell.Offset（0，4）.Value =resolved或者afCell.Offset（0，4）.Value _ =Resolved或者afCell.Offset（0,2）.Value =Resolved或者afCell。 Offset（0，2）.Value = _resolved）然后
 afCell.EntireRow.Delete 
 End If 
 Next afCell 
  
解决方案
您需要一个循环，并使用行号，因为你需要操纵你当前在循环中的位置，也Ť他终点; 
 
例如 
 
 
  Dim lRow as Long 
 lRow = 1 
直到lRow>如果（afCell.Offset（0，4）.Value =resolved或者afCell.Offset（0，4）.Value _（）返回
设置afCell = Cells（lRow，1）
 
。 =已解决或者afCell.Offset（0,2）.Value =Resolved或者afCell.Offset（0,2）.Value = _resolved）然后
 afCell.EntireRow.Delete 
 
'减去最后一行，因为我们删除了一行
 lLastRow = lLastRow  -  1 
 else 
'递增行号移动到下一行
 lRow = lRow + 1 
 End IF 
 Loop 
  
注意：这是完全未经测试的所以你需要调试它，但你应该得到的主意。
 
Below is my code. I am trying to move through some rows, and if there is certain data there, then remove those rows, however the current way I have coded it, whenever I delete a row, I skip the one below it. I changed the range to go high numbered rows to low number rows, but my macro still starts at the top and moves down. I thought if I had it move up, a deletion would not cause it to skip the next item. How can I make it move from the bottom of the list upwards, or what is a better way to do this? 
My code is below:
Dim lLastRow As Long
Dim num As Integer
Dim name As String
Dim rCell As Range
Dim afCell As Range
Dim rRng As Range
Dim affectedRng As Range

Windows("Most Affected Customer Info.xls").Activate
Worksheets("Sheet 1").Activate
Cells(1, 1).Select
Selection.End(xlDown).Select
lLastRow = ActiveCell.Row

Set affectedRng = Range("A" & lLastRow & ":A2")
'First remove resolved entries
For Each afCell In affectedRng
    If (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved") Then
            afCell.EntireRow.Delete
    End If
Next afCell

解决方案
You need a do loop and to use the row number as you need to manipulate both your current position in the loop and also the end point;

e.g.
Dim lRow as Long
lRow = 1
Do Until lRow > lLastRow
    Set afCell = Cells(lRow ,1)

    If (afCell.Offset(0, 4).Value = "resolved" Or afCell.Offset(0, 4).Value _ = "Resolved" Or afCell.Offset(0, 2).Value = "Resolved" Or afCell.Offset(0, 2).Value = _ "resolved") Then
        afCell.EntireRow.Delete

        'Decrement the last row as we've removed a row
        lLastRow = lLastRow - 1
    else
        'Increment the row number to move to the next one
        lRow = lRow + 1
    End IF
Loop
Note: this is completely untested so you'll need to debug it, but you should get the gist.

                        
这篇关于删除行而不跳过一行如何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！