Deleting rows within a named range

[Guest]

I have the following code

For Each r In myRange
If LCase(oWS.Name) = "status" Then
Debug.Print r.Address, myRange.Address
If r.Text <> fNameNew Then
r.EntireRow.Delete
End If
End If
Next r


Let's say myRange.address = B5:B23

If row 6 is deleted, the next r.address that's reviewed is B7, not a repeat
of B6. What do I need to do to fix this?

Thanks,
Barb Reinhardt

 

[Guest]

Don't delete rows within the For loop. Instead, build a new range in the
loop and delete afterwards

Sub dural()
Dim rr As Range
Set rr = Range("A65536")
For Each r In myRange
If LCase(oWS.Name) = "status" Then
Debug.Print r.Address, myRange.Address
If r.Text <> fNameNew Then
Set rr = Union(rr, r)
End If
End If
Next r
rr.EntireRow.Delete
End Sub

 

[JW]

You could iterate through your for loop using an integer and the count
of items in your range.
Something like:
Sub tester()
Dim myRange As Range, r As Range
Dim i As Integer
Set myRange = Range("B5:B23")
For i = 1 To myRange.Count
For Each r In myRange
If r.Text = "status" Then
r.EntireRow.Delete
i = i - 1
End If
Next r
Next i
End Sub

Of course, I am programatically creating the myRange dimension, but a
named range should work the same way.

HTH
-Jeff-

 

[JW]

Oops. I forgot to add that I changed the code around a bot for
testing purposes. You would ned something like this:
Sub tester()
Dim myRange As Range, r As Range
Dim i As Integer
Set myRange = Range("B5:B23")
For i = 1 To myRange.Count
For Each r In myRange
If LCase(oWS.Name) = "status" Then
Debug.Print r.Address, myRange.Address
If r.Text <> fNameNew Then
r.EntireRow.Delete
End If
End If
Next r
Next i
End Sub

 

[Guest]

Thanks. I'll do that.

Don't delete rows within the For loop. Instead, build a new range in the
loop and delete afterwards

Sub dural()
Dim rr As Range
Set rr = Range("A65536")
For Each r In myRange
If LCase(oWS.Name) = "status" Then
Debug.Print r.Address, myRange.Address
If r.Text <> fNameNew Then
Set rr = Union(rr, r)
End If
End If
Next r
rr.EntireRow.Delete
End Sub