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
精彩评论