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!

Do it in reverse to cure the problem

Sub deleteit()
lastrowcola = Range("A65536").End(xlUp).Row '<Alter to suit
For x = lastrowcola To 1 Step -1
Cells(x, 1).Select
MyPos = InStr(1, Cells(x, 1).Value, strWhat, 1)
If MyPos > 0 Then
Response = MsgBox("Delete?", vbYesNo + vbQuestion)
If Response = vbYes Then
Selection.EntireRow.Delete
End If
End If
Next

End Sub

Mike


"SueCool" wrote:

> 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!

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" <(E-Mail Removed)> wrote in message
news:2E10B346-E99D-441A-AB69-(E-Mail Removed)...
>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!

Thanks Mike - that's done the job. Luckily the strings I am searching for
are in the first column, but how would I search all the cells in each row?

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


"Mike H" wrote:

> Do it in reverse to cure the problem
>
> Sub deleteit()
> lastrowcola = Range("A65536").End(xlUp).Row '<Alter to suit
> For x = lastrowcola To 1 Step -1
> Cells(x, 1).Select
> MyPos = InStr(1, Cells(x, 1).Value, strWhat, 1)
> If MyPos > 0 Then
> Response = MsgBox("Delete?", vbYesNo + vbQuestion)
> If Response = vbYes Then
> Selection.EntireRow.Delete
> End If
> End If
> Next
>
> End Sub
>
> Mike
>
>
> "SueCool" wrote:
>
> > 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!

Thanks Vasant - the logic seemed sound but something strange seemed to happen
when I tried it.

I've got a fix now, so thanks anyway,

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


"Vasant Nanavati" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:2E10B346-E99D-441A-AB69-(E-Mail Removed)...
> >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!
>
>
>

Sue,

Put another for/next loop in

Cells(x, 1).Select
for y = 1 to 256
MyPos = InStr(1, Cells(x, y).Value, strWhat, 1)
If MyPos > 0 Then
Response = MsgBox("Delete?", vbYesNo + vbQuestion)
If Response = vbYes Then
Selection.EntireRow.Delete
next y


Not tested but sjould work,

Mike
"SueCool" wrote:

> Thanks Mike - that's done the job. Luckily the strings I am searching for
> are in the first column, but how would I search all the cells in each row?
>
> Sue
> --
> I''''m not a complete idiot - there are some parts missing!
>
>
> "Mike H" wrote:
>
> > Do it in reverse to cure the problem
> >
> > Sub deleteit()
> > lastrowcola = Range("A65536").End(xlUp).Row '<Alter to suit
> > For x = lastrowcola To 1 Step -1
> > Cells(x, 1).Select
> > MyPos = InStr(1, Cells(x, 1).Value, strWhat, 1)
> > If MyPos > 0 Then
> > Response = MsgBox("Delete?", vbYesNo + vbQuestion)
> > If Response = vbYes Then
> > Selection.EntireRow.Delete
> > End If
> > End If
> > Next
> >
> > End Sub
> >
> > Mike
> >
> >
> > "SueCool" wrote:
> >
> > > 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!

Thanks Mike - you are a star, but that was a bit of an easy one!

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


"Mike H" wrote:

> Sue,
>
> Put another for/next loop in
>
> Cells(x, 1).Select
> for y = 1 to 256
> MyPos = InStr(1, Cells(x, y).Value, strWhat, 1)
> If MyPos > 0 Then
> Response = MsgBox("Delete?", vbYesNo + vbQuestion)
> If Response = vbYes Then
> Selection.EntireRow.Delete
> next y
>
>
> Not tested but sjould work,
>
> Mike
> "SueCool" wrote:
>
> > Thanks Mike - that's done the job. Luckily the strings I am searching for
> > are in the first column, but how would I search all the cells in each row?
> >
> > Sue
> > --
> > I''''m not a complete idiot - there are some parts missing!
> >
> >
> > "Mike H" wrote:
> >
> > > Do it in reverse to cure the problem
> > >
> > > Sub deleteit()
> > > lastrowcola = Range("A65536").End(xlUp).Row '<Alter to suit
> > > For x = lastrowcola To 1 Step -1
> > > Cells(x, 1).Select
> > > MyPos = InStr(1, Cells(x, 1).Value, strWhat, 1)
> > > If MyPos > 0 Then
> > > Response = MsgBox("Delete?", vbYesNo + vbQuestion)
> > > If Response = vbYes Then
> > > Selection.EntireRow.Delete
> > > End If
> > > End If
> > > Next
> > >
> > > End Sub
> > >
> > > Mike
> > >
> > >
> > > "SueCool" wrote:
> > >
> > > > 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!

You might want to exit your second loop when you delete the row.


--
Regards,
Tom Ogilvy


"SueCool" wrote:

> Thanks Mike - you are a star, but that was a bit of an easy one!
>
> Sue
> --
> I''''m not a complete idiot - there are some parts missing!
>
>
> "Mike H" wrote:
>
> > Sue,
> >
> > Put another for/next loop in
> >
> > Cells(x, 1).Select
> > for y = 1 to 256
> > MyPos = InStr(1, Cells(x, y).Value, strWhat, 1)
> > If MyPos > 0 Then
> > Response = MsgBox("Delete?", vbYesNo + vbQuestion)
> > If Response = vbYes Then
> > Selection.EntireRow.Delete
> > next y
> >
> >
> > Not tested but sjould work,
> >
> > Mike
> > "SueCool" wrote:
> >
> > > Thanks Mike - that's done the job. Luckily the strings I am searching for
> > > are in the first column, but how would I search all the cells in each row?
> > >
> > > Sue
> > > --
> > > I''''m not a complete idiot - there are some parts missing!
> > >
> > >
> > > "Mike H" wrote:
> > >
> > > > Do it in reverse to cure the problem
> > > >
> > > > Sub deleteit()
> > > > lastrowcola = Range("A65536").End(xlUp).Row '<Alter to suit
> > > > For x = lastrowcola To 1 Step -1
> > > > Cells(x, 1).Select
> > > > MyPos = InStr(1, Cells(x, 1).Value, strWhat, 1)
> > > > If MyPos > 0 Then
> > > > Response = MsgBox("Delete?", vbYesNo + vbQuestion)
> > > > If Response = vbYes Then
> > > > Selection.EntireRow.Delete
> > > > End If
> > > > End If
> > > > Next
> > > >
> > > > End Sub
> > > >
> > > > Mike
> > > >
> > > >
> > > > "SueCool" wrote:
> > > >
> > > > > 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!