Decrementing row after deletion
 

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!

Decrementing row after deletion
 

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!

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" wrote in message
...
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!

Decrementing row after deletion
 

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!

Decrementing row after deletion
 

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" wrote in message
...
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!

Decrementing row after deletion
 

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!

Decrementing row after deletion
 

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!

Decrementing row after deletion
 

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!