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

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

--
Gary''s Student - gsnu200719


"Barb Reinhardt" wrote:

> 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

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-

Barb Reinhardt wrote:
> 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

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

JW wrote:
> 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-
>
> Barb Reinhardt wrote:
> > 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

Thanks. I'll do that.

"Gary''s Student" wrote:

> 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
>
> --
> Gary''s Student - gsnu200719
>
>
> "Barb Reinhardt" wrote:
>
> > 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