开发者

Removing rows without skipping a row how to?

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 a开发者_JAVA百科fCell 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


How about going through the list backwards?

EDIT Some code to try

For row = lLastRow To 1 Step -1
    If Range("D" & row).Value = "resolved" Then Rows(row).EntireRow.Delete
Next row

I tested this on a small case with "resolved" in column D and it worked like a champ. You might find that the code both does the trick and reads well.


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.


You could replace your For Each with a do while, like this:

rowx = 2
Do While rowx < llastrow
    If Range("B" & rowx).Value = "resolved" Then 'replace this with the columns you're checking
        Rows(rowx).EntireRow.Delete
    Else
        rowx = rowx + 1
    End If

Loop


You could try to change the If condition with While loop.

For Each afCell In affectedRng
    r = afCell.Row
    c = afCell.Column
    While (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") 
       afCell.EntireRow.Delete
       Set afCell = Cells(r, c)
    Wend
Next afCell
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜