Re: Decrementing row after deletion

Try going backwards (also, no need to select anything):

Something like (untested):

Dim i As Long
For i = myRange.Rows.Count to 1 Step -1
MyPos = InStr(1, myRange(i).Valu, strWhat, 1)
If MyPos > 0 Then
Response = MsgBox("Delete?", vbYesNo + vbQuestion)
If Response = vbYes Then
myRange(i).EntireRow.Delete
End If
End If
Next

________________________________________________________________________



"SueCool" <SueCool@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E10B346-E99D-441A-AB69-FA82E79A9D39@xxxxxxxxxxxxxxxx
I use the following code to delete a row containing a particular string,
but
if two succesive rows contain the string, the second row is skipped. How
can
I decrement the row counter, so that after a deletion the next for loop
works
on the same row?

For Each C In myRange
C.Select
MyPos = InStr(1, C.Value, strWhat, 1)
If MyPos > 0 Then
Response = MsgBox("Delete?", vbYesNo + vbQuestion)
If Response = vbYes Then
Selection.EntireRow.Delete
End If
End If
Next

Any help would be much appreciated,

Sue

--
I''m not a complete idiot - there are some parts missing!


.

Relevant Pages

  • RE: Decrementing row after deletion
    ... MyPos = InStr.Value, strWhat, 1) ... If MyPos> 0 Then ... If Response = vbYes Then ... so that after a deletion the next for loop works ...
    (microsoft.public.excel.programming)
  • Re: Decrementing row after deletion
    ... Thanks Vasant - the logic seemed sound but something strange seemed to happen ... If MyPos> 0 Then ... If Response = vbYes Then ...
    (microsoft.public.excel.programming)
Loading