Code and thanks for/to Tom and Frank

[Dan]

Hello again:

Thanks again guys but is not working:

So I have a big file; here are just few rows...

1 1 OK
2 2 OK
3 4 NO
2 2 OK
5 6 NO
******************

The code taht I would need is to search for no and when is
found to insert a row above

Thanks again and I am still testing those codes you gave
me but does not produce any result?

Sub InsertRowBeforeNo()
Dim rng as Range, i as Long
set rng = cells(rows.count,3).End(xlup)
for i = rng.row to 2 step -1
if Ucase(cells(rng.row,3).Value) = "NO" then
cells(rng.row,3).EntireRow.Insert
end if
Next
End Sub

Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index+1, "C").Value ="No" Then
Cells(row_index + 1, "C").EntireRow.Insert
(xlShiftDown)
End If
Next
End Sub

Regards,

Dan

 

[Tom Ogilvy]

Mine should actually be:

Sub InsertRowBeforeNo()
Dim rng As Range, i As Long
Set rng = Cells(Rows.Count, 3).End(xlUp)
For i = rng.Row To 2 Step -1
If UCase(Cells(i, 3).Value) = "NO" Then
Cells(i, 3).EntireRow.Insert
End If
Next
End Sub

That worked fine for me. Assumes NO and YES are in the third column.

Frank's doesn't work because NO <> No.

 

[Frank Kabel]

Hi
what does happen exactly?

 

[Dan]

Hi again:

Frank, yours is working now, but my mistake, I would need
inserted not the whole row; just for the 3 columns: sort
of shift cells down; there are other columns after c and I
have autofilter; something like belowlike below...
1 1 OK a
2 2 OK a
b
3 4 NO c
57 7 NO a


Thank you very much,

Dan

PS Tom your did not have a result...

 

[Tom Ogilvy]

PS: Dan, it worked fine for me and the technique is identical to Frank's,
so Don't think the problem is my code - at least not with the information
you presented.

Please try to stay in the thread and not start new threads without context.